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.