Oracle Check constraint that compares dates column from another table-CodePudding

I want to add a check constraint to a table that needs comparison of a column to another table.

This is the select statement

select * from a join b
on a.id = b.id
where a.valid_to <= b.end_date;

and the rows that violates this rule if a.valid_to > b.end_date then that should not be entered into the db.

How can I create this check contraint?

If I have to create a function with this select statement, please help with the syntax. TIA!

CodePudding user response：

A trigger, as you were told.

Sample tables:

SQL> create table test_a
  2  (id       number,
  3   valid_to date);

Table created.

SQL> create table test_b
  2  (id       number,
  3   end_date date);

Table created.

Trigger on table test_a:

SQL> create or replace trigger trg_biu_ta
  2    before insert or update on test_a
  3    for each row
  4  declare
  5    l_num number;
  6  begin
  7    select 1
  8      into l_num
  9      from test_b b
 10      where b.id = :new.id
 11        and :new.valid_to <= b.end_date
 12        and rownum = 1;
 13  exception
 14    when no_data_found then
 15      raise_application_error(-20000, 'VALID_TO must be lower then its END_DATE (or END_DATE does not exist)');
 16  end;
 17  /

Trigger created.

Testing: this a reference record:

SQL> insert into test_b (id, end_date) values (1, date '2022-06-25');

1 row created.

Inserting ID that doesn't exist in test_b:

SQL> insert into test_a (id, valid_to) values (2, date '2022-05-15');
insert into test_a (id, valid_to) values (2, date '2022-05-15')
            *
ERROR at line 1:
ORA-20000: VALID_TO must be lower then its END_DATE (or END_DATE does not
exist)
ORA-06512: at "SCOTT.TRG_BIU_TA", line 12
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_TA'

Inserting ID that exists, but with an invalid date value:

SQL> insert into test_a (id, valid_to) values (1, date '2022-10-15');
insert into test_a (id, valid_to) values (1, date '2022-10-15')
            *
ERROR at line 1:
ORA-20000: VALID_TO must be lower then its END_DATE (or END_DATE does not
exist)
ORA-06512: at "SCOTT.TRG_BIU_TA", line 12
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_TA'

This is OK: ID exists, valid_to is lower than appropriate end_date:

SQL> insert into test_a (id, valid_to) values (1, date '2022-04-11');

1 row created.

SQL>