There is no unique constraint, even though I'm referencing primary keys?

Question

Lets say I have:

CREATE TABLE A (
   a int, 
   b int, 
   PRIMARY KEY(a, b)
);

This works successfully. Now I have a and b as the primary keys (which implies that they are unique).

Then I do:

CREATE TABLE B (a int, 
   b int, 
   FOREIGN KEY(a) REFERENCES A(a), 
   FOREIGN KEY(b) REFERENCES A(b)
);

But this gives me an error there is no unique constraint matching given keys for referenced table "a"

That's not how foreign key constraints work. Where you have a composite primary key you need to reference the entire key from your foreign key constraint. — Richard Hansell, Mar 05 '18 at 16:34
It's a shame this is a duplicate because this question is a better question than the duplicate. It has less going on and is more of a `Minimal, Complete, and Verifiable example`. — Jamie Twells, Mar 05 '18 at 16:45

Jamie Twells · Answer 1 · 2018-03-05T16:43:16.237

1

You've said the combination of a and b make a unique key, but that doesn't imply that both a and b are unique, for example:

a | b
-----
1 | 2
1 | 3
2 | 2

would be valid in your table A, but then which row would the following row reference in B?

a | b
-----
1 | 2

a could reference 1,2 or 1,3 and b could reference 1,2 or 2,2

That is what the error is complaining about.

edited Mar 05 '18 at 16:43

answered Mar 05 '18 at 16:38

Jamie Twells

1 Answers1