2个表之间的Oracle SQL Check约束 [英] Oracle SQL Check constraint between 2 tables

问题描述

我有2张桌子Persons和Relationships.

Persons表目前只有2个字段:ID和Age.

The Persons table got only 2 fields for now: ID and Age.

Relationships具有3个字段:Person_ID，Relative_ID和Relation

Relationships have 3 fields: Person_ID, Relative_ID and Relation

我想做的很简单:在插入\更新到Relationships时，我要检查以下内容:

What I wanna do is simple: on insertion\update to Relationships I want to check the following:

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

谢谢

推荐答案

检查约束(使用其技术含义中的短语)除引用约束外，无法对来自两个不同表的数据的约束进行编码.

Check constraints (using the phrase in its technical meaning), with the exception of referential constraints, can't encode constraints on data from two different tables.

您可以使用的一种技术是创建实例化视图，并对视图进行约束.例如:

One technique you can use is to create a materialized view and have a constraint on the view. For example:

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

当然，您将必须首先创建实例化视图日志，依此类推.因此，对于每个INVALID关系，该实例化视图将有一行.然后在实例化视图上创建约束，例如，条件flag = 0. (或者，更简单一些:将MV更改为select null as flag from....，并将列flag设置为MV中的not null.)

Of course, you will have to create materialized view logs first, etc. So, this materialized view will have one row for each INVALID relationship. Then create a constraint on the materialized view, for example for the condition flag = 0. (Or, a bit simpler: change the MV to select null as flag from.... and make the column flag to be not null in the MV.)

每当尝试在relationships表中插入无效的行时，实例化视图都将更新为新的行；但是MV上的检查约束阻止了这种情况，因此整个交易将失败.

Whenever an attempt to insert an invalid row into the relationships table is made, the materialized view would be updated with a new row; but the check constraint on the MV prevents that, so the entire transaction will fail.

这篇关于2个表之间的Oracle SQL Check约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！