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.
Best Answer
It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a
UNIQUE
constraint on(a1, a2)
, we can't add aFOREIGN KEY
thatREFERENCES (a2, a1)
unless there is a unique constraint on that(a2, a1)
that essentially is redundant.It wouldn't be terrifically difficult to add this as a feature:
or the generalization:
It seems that it hasn't been asked or it hasn't been considered high enough priority to be implemented.
You can always make a request - in the respective channel - for the feature to be implemented. Or even implement it yourself, if the DBMS is open source, like Postgres.