Unique key based on many-to-many pivot table

Question

I have to manage artists and albums tables:

| artists      |  | albums       |  | album_artist |
+--------------+  +--------------+  +--------------+
| id           |  | id           |  | id           |
| artist       |  | album        |  | album_id     |
| created_at   |  | created_at   |  | artist_id    |
| updated_at   |  | updated_at   |  +--------------+
+--------------+  +--------------+

Keeping in mind that this is a many-to-many relation, I do need to find a way to get the album-artist(s) pair unique, since albums may have the same name yet belonging to different artists (f.e. "Greatest Hits" album of 2Pac and "Greatest Hits" of Notorious BIG).

Is there a known way/pattern to address this problem?

score 5 · Accepted Answer · edited Apr 13 '17 at 12:42

The simplest and probably most common way would be to declare the (album_id, artist_id) pair of columns a unique composite key.

There are two variations of this approach. First, you can keep the current structure of the album_artist table and simply add a unique constraint on the said two columns:

ALTER TABLE album_artist
ADD CONSTRAINT uq_album_artist
  UNIQUE (album_id, artist_id);

There are more options to adding a UNIQUE constraint, which can be found in the linked manual.

The second variation is to get rid of the id column and declare (album_id, artist_id) to be the primary key of the table, as Rick James has suggested in a comment:

ALTER TABLE album_artist
DROP PRIMARY KEY;  /* assuming id has actually been declared
                      as the PK; if not, omit this step */

ALTER TABLE album_artist
DROP id;

ALTER TABLE album_artist
ADD CONSTRAINT pk_album_artist
  PRIMARY KEY (album_id, artist_id);

A junction table like your album_artist table, which stores no additional information beyond the table references, usually does not need to be referenced by other tables – at least, not often enough to justify the need for a dedicated ID column. The second variation, therefore, may be more appropriate. Rick's other useful thoughts on implementation of a junction table can be found in his blog.

If another table needs to reference an album and an artist as a valid combination (i.e. one that exists in the junction table), it can simply use a composite foreign key to reference album_artist:

FOREIGN KEY (album_id, artist_id) REFERENCES album_artist (album_id, artist_id)

It is only when you need to reference the junction table often (that is, from many tables) that I would argue the first approach, which allows you to keep a dedicated PK column in the junction table, would be more useful.

Unique key based on many-to-many pivot table

1 Answers1