SQL – referential integrity – Foreign key and Check constraint

referential-integrity

Please let me know if below two declarations are same.

create table R1
( a INT PRIMARY KEY, 
  b INT references K(w));

Note: K is a table with single attribute w as primary key

create table R1
( a INT PRIMARY KEY, 
  b INT CHECK (b in (SELECT w FROM K));

Best Answer

To answer the question of whether they are equivalent, you need to declare K. Assuming:

create table K (x int not null primary key, w int, unique(w));
insert into K (x) values (1);
insert into K (x) values (2);

The following would satisfy the CHECK constraint:

insert into R1 (a,b) values (1,1);

because:

CHECK (b in (SELECT w FROM K))

evaluates to:

CHECK (b in (null, null)) <=> CHECK (null)

The rule is that the constraint must not evaluate to false. I don't know any DBMS that support subqueries in CHECK constraints, so I can't verify that.

I tested the foreign key variant against PostgreSQL 11, and the insert is rejected

 ERROR:  insert or update on table "r1" violates foreign key constraint "r1_b_fkey" DETAIL:  Key (b)=(1) is not present in table "k".
Related Question