Composite keys and unique constrains performances and alternatives-CodePudding

I'm creating a database using MySQL for a music streaming application for my school project. It has a table "song_discoveries" which has these columns: user_id, song_id and discovery_date. It has no primary key. The "user_id" and "song_id" are foreign keys and the "discovery_date" is self explanatory. My problem is that I want to ensure that there are no duplicate rows in this table since obviously a user can discover a song once, but I'm not sure on whether to use a unique constraint for all of the columns or create a composite primary key of all columns. My main concerns are what is the best practice for this and which has better performance? Are there any alternatives to these approaches?

I tried searching for answers to my question but I read a lot of different opinions on the composite keys and constrains so I'm not sure what to do anymore.

CodePudding user response：

If the goal is to create "no duplicate rows in this table". Then to do this, you need to identify what makes a "unique" record. If uniqueness is guaranteed by the composite user_id, discovery_date and song_id that that should be your primary composite key. Thinking a bit more, if we apply a rule that says, "a song can only be discovered once !" then your composite primary key should be user_id,song_id (this will guarantee that you don't add the same song multiple times), but if you can discover the same song on multiple days, then you can leave the key as the composition of the 3 fields. If you go with user/song then a table can look like this:

CREATE TABLE song_discoveries (
    user_id int NOT NULL,
    song_id int NOT NULL,
    discovery_date DATE NOT NULL,
    PRIMARY KEY (user_id, song_id)
);

CodePudding user response：

In MySQL, a table is stored as a clustered index sorted by the primary key.

If the table has no primary key, but does have a unique key constraint on non-NULL columns, then the unique key becomes the clustered index, and acts almost exactly the same as a primary key. There's no performance difference between these two cases.

The only difference between a primary and a unique key on non-NULL columns is that you can specify the name of the unique key, but the primary key is always called PRIMARY.