0

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));
  • If K(w) is a primary or unique key, they are similar declarations. But most SQL products accept only the first syntax (FOREIGN KEY constraint) and not the second (which has a subquery in a CHECK constraint) – ypercubeᵀᴹ Jun 16 '14 at 8:52
0

Not exactly - with your second CREATE statement, the same value of b can occur multiple times in table K, whereas with the first statement, it will occur once and once only. It's unusual - what are you trying to achieve?

0

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".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.