Hey, hackers!
While working with some foreign keys, I noticed some mildly unexpected
behavior. The columns referenced by a unique constraint must naturally
have a unique constraint on them:
CREATE TABLE foo (a integer);
CREATE TABLE bar (x integer REFERENCES foo(a));
> ERROR: there is no unique constraint matching given keys for referenced
table "foo"
But Postgres doesn't allow a foreign key to reference a set of columns
without a unique constraint, even if there a unique constraint on a
subset of those columns (i.e., it doesn't allow referencing a superset
of a unique constraint).
CREATE TABLE foo (a integer PRIMARY KEY, b integer);
CREATE TABLE bar (x integer, y integer, FOREIGN KEY (x, y) REFERENCES
foo(a, b));
> ERROR: there is no unique constraint matching given keys for referenced
table "foo"
It seems to me like there would be nothing wrong in this case to allow this
foreign key constraint to exist. Because there is a unique constraint on foo(a),
foo(a, b) will also be unique. And it doesn't seem like it would be too complex
to implement.
Neither MATCH SIMPLE nor MATCH FULL constraints would have any issues
with this. MATCH PARTIAL may, but, alas, it's not implemented. (I've had
a few ideas about foreign keys, and MATCH PARTIAL seems to always come
up, and I still don't understand what its use case is.)
A real-world use case that uses denormalization could run into this. Imagine a
naive music database that has a list of artists, albums, and songs, where each
album is by one artist and each song is on one album, but we still store a
reference to the artist on each song:
CREATE TABLE artists (id serial PRIMARY KEY, name text);
CREATE TABLE albums (id serial PRIMARY KEY, artist_id REFERENCES
artists(id) name text);
CREATE TABLE songs (
id serial PRIMARY KEY,
artist_id REFERENCES artists(id) ON DELETE CASCADE,
album_id REFERENCES albums(id) ON DELETE CASCADE,
name text,
);
To ensure that artist deletions are fast, we need to create an index on
songs(artist_id) and songs(album_id). But, suppose we wanted to save on index
space, and we never needed to query JUST by album_id. We could then do:
CREATE TABLE songs (
id serial PRIMARY KEY,
artist_id REFERENCES artists(id) ON DELETE CASCADE,
album_id integer,
name text,
FOREIGN KEY (artist_id, album_id) REFERENCES albums(artist_id, id)
ON DELETE CASCADE
);
And then we could have a single index on songs(artist_id, album_id) that would
serve both ON CASCADE DELETE triggers:
-- Delete artist
DELETE FROM songs WHERE artist_id = <artist.id>;
-- Delete artist
DELETE FROM songs
WHERE artist_id = <album.artist_id> AND album_id = <album.id>;
But Postgres wouldn't let us create the composite foreign key described.
It seems like a somewhat useful feature. If people think it would be useful to
implement, I might take a stab at it when I have time.
- Paul
