SQL - referential integrity - Foreign key and Check constraint 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));
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)) &lt;=&gt; 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".