预言机:检查时间限制

Question

我有一个start_session和end_session。

我想通过约束确保end_session在start_session后 60 分钟。

这可能吗，如下所示

ALTER TABLE BOOKING_SESSION ADD CONSTRAINT SESSION_LENGHT CHECK (END_SESSION > START_SESSION + 60 MINUTES );

我得到了以下触发器来检查开始会话是否不大于结束会话（感谢贾斯汀洞穴）

CREATE OR REPLACE TRIGGER trg_check_dates
  BEFORE INSERT OR UPDATE ON BOOKING_SESSION
  FOR EACH ROW
BEGIN
  IF( :NEW.END_SESSION < :NEW.START_SESSION )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
          'Invalid: END_SESSION must be greater than START_SESSION = ' || 
          to_char( :NEW.START_SESSION, 'YYYY-MM-DD HH24:MI:SS' ));
  END IF;
 END;
 /

但是，我需要确保结束会话在start_session后一小时的那个

Answer 1

以下是如何使用Check约束来施加这种限制：

SQL> create table Tb_test(
  2    id number not null primary key,
  3    start_session timestamp,
  4    end_session   timestamp
  5  )
  6  ;
Table created
SQL> 
SQL> alter table tb_test
  2    add constraint CHK_SES_TIME
  3  check ((end_session is null) or ((end_session - start_session) >= interval '1' hour))
  4  ;
Table altered
-- data that is violate our constraint
SQL> insert into tb_test(id, start_session, end_session)
  2    values(1, systimestamp, systimestamp)
  3  ;
insert into tb_test(id, start_session, end_session)
  values(1, systimestamp, systimestamp)
ORA-02290: check constraint (HR.CHK_SES_TIME) violated
-- valid data 
SQL> insert into tb_test(id, start_session, end_session)
  2    values(1, systimestamp, systimestamp + interval '1' hour)
  3  ;
1 row inserted 
-- only start session added, end_session is null.
SQL> insert into tb_test(id, start_session)
  2    values(2, systimestamp)
  3  ;
1 row inserted
SQL> commit;
Commit complete
-- updating end_session with data that violate check constraint
SQL> update tb_test
  2    set end_session = systimestamp;
update tb_test
  set end_session = systimestamp
ORA-02290: check constraint (HR.CHK_SES_TIME) violated
-- updating end_session with valid data

SQL> update tb_test
  2    set end_session = systimestamp + interval '1' hour;
 1 row updated

在上面的示例中，start_session 列和end_session列的数据类型timestamp。如果将它们声明为Date数据类型，则check约束的公式可能是：

(end_session - srtart_session) >= 1/24