Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 also 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 an m to m relationship.

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

share|improve this question
    
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
    
add comment

1 Answer 1

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
) ;
share|improve this answer
1  
+1 Would it make sense to you to add the following unique constraints to AlbumTrack table: (trackID, albumID) and (albumID, trackNo)? –  AlexKuznetsov 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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