19

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.

Improve this question
0

3 Answers 3

Reset to default
17

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 a FOREIGN KEY that REFERENCES (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:

When there is a UNIQUE constraint on (a), then any (a, b, c, ..., z) or (b,c, ...a, ...z) combination is also guaranteed UNIQUE.

or the generalization:

When there is a UNIQUE constraint on (a1, a2, ..., aN), then any (a1, a2, ..., aN, b1, b2, ..., bM) combination or any rearrangement is also guaranteed UNIQUE.

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.

Improve this answer
4
  • I'm not sure it would be quite this simple.. What about partial indexes, or NULL values? etc.. NULL might still work fine if you're happy with NULL != NULL. Anyway.. :) Commented Oct 26, 2016 at 16:56
  • @JoishiBodio I don't think Nulls are a problem. UNIQUE constraints can be defined or nullable columns as well. The default is that if any column has a NULL, then the constraint is passed and the row accepted. Commented Oct 26, 2016 at 16:58
  • On second though, if a1,a2,...aN are not nullable and b1,b2,bM are we may get into problems. But the feature could surely be implemented for not-nullable columns. What is probably worrisome is the efficiency implications. Commented Oct 26, 2016 at 17:00
  • I'm familiar with UNIQUE INDEX where columns are NULLABLE .. which is why I mentioned it. :) But I agree - in the case that there are no NULLs (and not a partial index either), it's probably fairly straightforward. Commented Oct 26, 2016 at 17:29
9

Foreign Keys in general (not just composite) MUST point to a UNIQUE KEY of some sort in another table. If they did not, there would be no relational data integrity.

This is complaining because, while you have a unique key on (id) .. you do NOT have a unique key on (id, num).. Thus, as far as the DB is concerned, the pair (id, num) is not GUARANTEED to be unique. Us, as humans, can figure out it will be unique, but I'm sure there would be a lot of additional code they would have to add to make Postgres smart enough to see that "oh hey .. id is supposed to be unique, so id,num should also be unique" ..

I would be highly surprised if they added that code when all you have to do is create another unique index on the two columns to fix the problem.

Just to be clear, the code they would have to add wouldn't be just this simple case... it would have to handle all cases, even ones where the foreign key is on 4+ columns, etc.. I'm sure the logic would be quite complex.

Improve this answer
1

It seems that while there aren't any obvious implementation issues, supporting this would violate the SQL standard (rather than just extending it), and that means it might be incompatible with future extensions of the standard.

There was an interesting thread in pgsql-hackers list on the topic, and that was the final word: link

Improve this answer
1
  • It wouldn't be a violation, just an extension in my opinion. But you do have a point. Commented Oct 8 at 18:00

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.