SQL约束，一个列值不能大于另一个表中的另一个值 [英] SQL Constraint that one column value cannot be greater than another in a different table

问题描述

这可能不是一个很好的问题，因为它与DB结构的混合业务逻辑，但它不是我的决定所以：

This probably isn't a very nice question as its blending business logic with DB structure, but its not my decision so:

可以定义一个约束推断出一列（表A，列X）的值不能大于通过外键引用的另一列（表B，列Y）的值：

Is it possible to define a constraint the infers the value of one column (Table A, Column X) cannot be greater than the value of another (Table B, Column Y) referenced via a foreign key:

TABLE_A
    ID (Primary Key)
    X (int value)
TABLE_B
    A_ID (Foreign Key to TABLE_A)
    Y (int value)

ie我想对Y的所有值强制执行，Y < L其中L是来自X的值，其中TABLE_B.A_ID == TABLE_A.ID

i.e. I want to enforce that for all values of Y, Y < L where L is a value from X where TABLE_B.A_ID == TABLE_A.ID

我正在使用DB2。

推荐答案

可以定义一个约束，推断一列（表A，列X）的值不能大于另一列（表B，Y列）通过外键引用：

Is it possible to define a constraint the infers the value of one column (Table A, Column X) cannot be greater than the value of another (Table B, Column Y) referenced via a foreign key:

否。这将需要在CHECK约束内使用SELECT语句，DB2不支持。

No. That would require using a SELECT statement within a CHECK constraint, and DB2 doesn't support that. If it did support using a SELECT statement that way, it would look something like this.

ALTER TABLE_B
ADD CONSTRAINT TABLE_B_Y_LT_L
CHECK (Y < (SELECT X FROM TABLE_A WHERE TABLE_A.ID = TABLE_B.A_ID));

SELECT语句将返回单个值，因为TABLE_A.ID是唯一的。但是，正如我所说，DB2不支持检查约束中的SELECT语句。我不认为当前的dbms有。

The SELECT statement would return a single value, because TABLE_A.ID is unique. But, like I said, DB2 doesn't support SELECT statements in check constraints. I don't think any current dbms does.

解决方法

几个解决方法。首先，你可以写一个触发器。第二，您可以在两个表中存储列X，并使用外键约束和检查约束来实现您的要求。

There are a couple of workarounds. First, you could write a trigger. Second, you could store column "X" in both tables, and use a foreign key constraint and a check constraint to implement your requirement.

-- Since "id" is unique, the combination of "id" and "x" is also unique. 
-- Declaring "unique (id, x)" lets these two columns be the target of a 
-- foreign key reference.
--
create table table_a (
    id integer primary key,
    x integer not null,
    unique (id, x)
);

-- The foreign key references both columns in "table_a". The check constraint
-- indirectly guarantees that y < table_a.x.
--
create table table_b (
    a_id integer not null,
    a_x integer not null,
    y integer not null,
    primary key (a_id, a_x, y),
    foreign key (a_id, a_x) references table_a (id, x),
    check (y < a_x)
);

这是标准SQL。它应该在任何当前的SQL dbms中工作。

This is standard SQL. It should work in any current SQL dbms.

这篇关于SQL约束，一个列值不能大于另一个表中的另一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！