英文:
Composite keys and unique constrains performances and alternatives
问题
我正在为我的学校项目创建一个使用MySQL的音乐流媒体应用程序的数据库。它有一个名为"song_discoveries"的表，其中包含这些列：user_id、song_id和discovery_date。它没有主键。"user_id"和"song_id"是外键，而"discovery_date"是不言自明的。我的问题是，我想确保在这个表中没有重复的行，因为显然一个用户只能发现一首歌，但我不确定是否应该对所有列使用唯一约束，还是创建一个所有列的复合主键。我的主要关切是什么是最佳实践，哪种方法性能更好？是否有其他替代方法？
英文:
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?
答案1
得分: 0
如果目标是创建“表中没有重复行”，那么需要确定什么使记录“唯一”。如果唯一性由复合的user_id、discovery_date和song_id保证，那么这应该是您的主要复合主键。
再深思熟虑一下，如果我们应用一个规则，即“一首歌只能被发现一次！”那么您的复合主键应该是user_id、song_id（这将保证您不会多次添加相同的歌曲），但如果您可以在多天内发现相同的歌曲，那么您可以将主键保留为这3个字段的组合。
如果您选择了user/song，那么表可以如下所示：
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)
);
英文:
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)
);
答案2
得分: 0
在MySQL中，表格被存储为按主键排序的_聚集索引_。
如果表格没有主键，但对非NULL列有唯一键约束，那么唯一键将成为聚集索引，并且几乎与主键完全相同。在这两种情况下，性能没有区别。
主键和非NULL列上的唯一键之间唯一的区别是你可以指定唯一键的名称，但主键始终称为PRIMARY。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论