16

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship.

Example: An artist has an album (the album cannot exist without an artist), the album also has many tracks, but the same track can exist in many albums.

So we have a many-to-many relationship between the album and the tracks.

If the album is a weak entity, then its primary key is a foreign key referencing the artist, thus it cannot be a foreign key to another table representing a many-to-many relationship.

The question is: is it possible to have this kind of relationship in SQL, and if so, how do I express it?

  • No, the album primary key would just be an integer that makes the album unique. You may then have a artist_id foreign key that references the artist. If you want a single track to be mapped to multiple albums then use a mapping table with track_id, album_id. Easy :) – Philᵀᴹ Feb 4 '13 at 2:59
16

I think you can, using a "diamond" relationship diagram:

diagram

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID)               -- this Unique constraint should be added
                                          -- if no track is allowed twice in an album
) ;
  • 1
    +1 Would it make sense to you to add the following unique constraints to AlbumTrack table: (trackID, albumID) and (albumID, trackNo)? – A-K Mar 6 '13 at 18:19
  • @AlexKuznetsov You are right, thnx. I'll "shrink" the PK to your suggested (albumID, trackNo) and add the other Unique constraint, too. – ypercubeᵀᴹ Jul 4 '13 at 20:56
  • 1
    Remember to allow for albums that have no single nominal artist, either by having a dummy artist called "Various" or similar or by making the artist column of the album table nullable. In fact, you could have more than one artist per track, so you might need a many-to-many arrangement there too. – David Spillett Sep 25 '14 at 13:01
  • 1
    @DavidSpillett Yes, we could do that but it would complicate things and deviate form the question asked. The question assumes/dictates that every album has a single artist. Not possible to have different artists per track, neither many artists per album or track. It's not a very good representation of the real world, indeed. – ypercubeᵀᴹ Sep 25 '14 at 13:07
  • 1
    @TimAbell I think that's a mishap from Workbench where the diagrams were created (not recognizing it the same as the Album-AlbumTrack connection due to the order of columns in the PKs) – ypercubeᵀᴹ Sep 25 '15 at 12:44
2

Unfortunately I don't have enough rep to comment on ypercubeᵀᴹ's answer, so I'll post an alternate answer instead - I agree with that answer in general but think the primary key and unique contraints on AlbumTrack are incorrect given that albums and tracks are both weak entities. For example, the following valid data would, with the prescribed constraints, be disallowed:

 artistID | albumID | trackID | trackNo 
----------+---------+---------+---------
        1 |       1 |       1 |       1
        2 |       1 |       1 |       1

Instead I would set PRIMARY KEY (artistID, albumID, trackID) and drop the unique constraint, resulting in:

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (artistID, albumID, trackID)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
) ;

Tracks are still constrained to occur at most once per album.

Also, the question doesn't actually specify that tracks are weak entities (only that albums are) - if tracks can in fact exist independent of artists, the Track and AlbumTrack tables are defined slightly differently:

CREATE TABLE Track
( trackID INT NOT NULL
, artistID INT
, title VARCHAR(100) NOT NULL
, PRIMARY KEY trackID
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (artistID, albumID, trackID)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (trackID)
    REFERENCES Track (trackID)
) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.