What kind of additional constraints can I use with PRIMARY KEY?

Question

I want my primary key to allow certain duplicate entries, how do I write this? I want a song to be allowed to have multiple genres.

CREATE TABLE songs (
    songid INTEGER PRIMARY KEY,
    title TEXT
)

CREATE TABLE genres (
    genreid INTEGER PRIMARY KEY,
    name TEXT


CREATE TABLE has_a_genre (
    songid INTEGER,
    genreid INTEGER,
    FOREIGN KEY (songid) REFERENCES songs (songid),
    FOREIGN KEY (genreid) REFERENCES genres (genreid)
)

This is a snippet of the code im working on. This code doesn't take a duplicate entry value. Let's say we input these values into the database: Genres (genreid, name) 1, rock 2, country 3, pop 4, electronic

Songs
(songid, title)
10001, Tomorrow
10002, Sweet Child O' Mine
10003, Faded

When I input in has_a_genre

(songid, genreid)
10001, 2
10002, 1
10003, 4
10003, 4

This doesn't work because my current code doesn't allow for multiple inputs of 10003, 4 How do I make it allow this?

Any basic, introductory manual, book or tutorial on relational databases will cover this. — Strawberry, Oct 17 '20 at 22:25

score 3 · Answer 1 · answered Oct 17 '20 at 22:25

3

You're describing a many-to-many relationship. This requires an additional table when using a relational database.

CREATE TABLE Songs (song_id INT PRIMARY KEY, ...attributes...);

CREATE TABLE Genres (genre_id INT PRIMARY KEY, ...attributes...);

CREATE TABLE SongGenres (
  song_id INT,
  genre_id INT, 
  PRIMARY KEY (song_id, genre_id),
  FOREIGN KEY (song_id) REFERENCES Songs(song_id),
  FOREIGN KEY (genre_id) REFERENCES Genres(genre_id)
);

You put one song_id, genre_id pair on each row of SongGenres. This allows a song to have multiple genres. There's no limit, because you can add as many rows as you need for each song.

It also makes it easy to query for all songs in a given genre.

answered Oct 17 '20 at 22:25

Bill Karwin

538,548
86
673
828

1

Thank you for this. What if I enter a genre_id that does not exist in SongGenre. Let's say Genres has genre_id from 1 to 18, but I enter 19 in SongGenres? or I enter a duplicate song_id and genre_id e.g (301289, 5) twice? – QCA Oct 17 '20 at 22:56
1

@QCA: you can't insert in `songgenres` a `genre_id` that does not exist in `genre` - the foreign key prevents that (you need to create the parent row in `genre` first). And: you can't insert duplicates `song_id, genre_id)` in `songgenres` (that tuple of column in the primary key of the table). I think Bill Karwin did answer the question that you asked here. – GMB Oct 17 '20 at 23:21
1

@GMB Can we add constraints to the parent table (genres) so that it will handle this? – QCA Oct 17 '20 at 23:26
I figured it out, didn't add PRIMARY KEY (song_id, genre_id) in my SongGenres table and other tables. damn – QCA Oct 18 '20 at 00:02

What kind of additional constraints can I use with PRIMARY KEY?

1 Answers1