Here is a simple table where records may reference parent records in the same table:
CREATE TABLE foo ( id SERIAL PRIMARY KEY, parent_id INT NULL, num INT NOT NULL, txt TEXT NULL, FOREIGN KEY (parent_id) REFERENCES foo(id) );
With the added requirement that one of the other field values (
num) must be identical between parent and child records, I thought a composite foreign key should do the trick. I changed the last line to
FOREIGN KEY (parent_id, num) REFERENCES foo(id, num)
and got ERROR: there is no unique constraint matching given keys for referenced table "foo".
I can easily add this constraint, but I don't understand why it is necessary, when one of the referenced columns (
id) is already guaranteed to be unique? The way I see it, the new constraint would be redundant.