SQL Constraint that one column value cannot be greater than another in a different table

Question

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

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)

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

I'm working with DB2.

Answer 1

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:

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));

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.

Workarounds

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)
);

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