2个表之间的Oracle SQL Check约束

Question

我得到了2个表，Persons和Relationships。

Persons表只有2个字段：ID和Age。

Relationships有3个领域：Person_ID，Relative_ID和Relation

我想要做的是简单的：在插入\更新Relationships我要检查以下内容：

if Relation == 'child' and Persons[Person_ID].Age < Persons[Relative_ID].Age: 
    Throw Exception 

感谢

Answer 1

你应该创建触发器。

试试这个

CREATE OR REPLACE TRIGGER my_trg 
       BEFORE INSERT OR UPDATE ON Relationships 
       FOR EACH ROW 
    declare 
    function i_Age(id int) return int is 
    li_res int; 
    begin 
     select p.Age 
     into li_res 
     from Persons p 
     where p.ID= id 
     and rownum=1; 
     return li_res; 
    exception when no_data_found then 
    return NULL; --or Throw Exception depend on your logic if some datas not found 
    end; 

    BEGIN 
     IF INSERTING OR UPDATING THEN 
      IF :NEW.Relation == 'child' and i_Age(:NEW.Person_ID) < i_Age(:NEW.Relative_ID) then 
      NULL; --Throw Exception or your logic 
      END IF; 
     END IF; 
    END; 

Answer 2

CHECK约束（使用其技术含义的短语），具有参照约束的例外，从两个不同的表上的数据无法编码的限制。

您可以使用的一种技术是创建物化视图并对视图进行约束。例如：

create materialized view relationship_check_mv 
build immediate 
refresh fast on commit 
as 
    select 1 as flag 
    from persons  p1 
     join 
     relationships r on p1.id = r.person_id 
     join 
     persons  p2 on p2.id = r.relative_id 
    where r.relationship = 'child' 
    and p1.age < p2.age 

当然，你必须创建物化视图日志第一，等等。所以，这个物化视图将不得不为每个无效的关系一行。然后在物化视图上创建一个约束，例如条件为flag = 0。 （或者，简单一点：改变MV以select null as flag from....，使列flag是not null在MV。）

每当插入一个无效的行到relationships表试图，物化视图将更新新的一排;但MV上的检查约束阻止了这一点，所以整个事务将失败。

Answer 3

CREATE OR REPLACE TRIGGER child_parent_age_tr BEFORE INSERT OR UPDATE ON RELATIONSHIPS FOR EACH ROW 
    DECLARE 
     child_age NUMBER; 
     parent_age NUMBER; 

    BEGIN 
     SELECT AGE INTO child_age FROM PERSONS WHERE ID = :NEW.PERSON_ID; 
     SELECT AGE INTO parent_age FROM PERSONS WHERE ID = :NEW.RELATIVE_ID; 
     IF INSERTING OR UPDATING THEN 
      IF :NEW.RELATION = 'child' AND child_age >= parent_age THEN 
       RAISE INVALID_NUMBER; 
      END IF; 
     END IF; 
    END;