Alberto Dallaporta Alberto Dallaporta - 2 years ago 96
SQL Question

MySql - Unique key based on many-to-many pivot table values

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?

Thank you.

Answer Source

I have done it before, with creating a unique constraint on the album_id,artist_id.

This will make it possible to have albums for different artists. As the artists id will differentiate. In your migration put.

$table->unique(['album_id', 'artist_id']);

In traditional database design, this is called a composite key, which is two or more columns, which made the table unique, in most pivot table this will be the two foreign keys.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download