主键应该是什么？
我遇到了一个我似乎无法解决的问题。
举例来说，我有一个包含即将发布的视频游戏版本的表：
GAME
game_ID | title
-----------------------------
1 | Super Mario
2 | Final Fantasy XIII
然后我有一个包含发布日期的表（只是为了论证，ps3 和 xbox360 的不同日期）：
RELEASES
game_ID | releasedate | platform
---------------------------------
1 | 20-04-2010 | Wii
2 | 23-03-2010 | PS3
3 | 20-03-2010 | Xbox360
现在，我将 game_ID 作为主键表“游戏”。并且game_ID也是表“RELEASES”中的外键。我应该将什么作为后者的主键？似乎没有必要在“RELEASES”表中创建另一个 ID 密钥？
我可以以某种方式使用 game_ID 和平台一起创建主键吗？如果是这样，SQL 会是什么样子？
I've ran into an issue that I can't seem to solve.
Say for instance that I have a table with upcoming videogame releases:
GAME
game_ID | title
-----------------------------
1 | Super Mario
2 | Final Fantasy XIII
And then I've got a table with releasedates (different dates for ps3 and xbox360 just for the sake of the argument):
RELEASES
game_ID | releasedate | platform
---------------------------------
1 | 20-04-2010 | Wii
2 | 23-03-2010 | PS3
3 | 20-03-2010 | Xbox360
Now, I have put game_ID as the primary key in the table "GAME". And game_ID is also a foreign key in the table "RELEASES". What should I have as the primary key in the latter? It seems rather unnecessary to create yet another ID-key in the "RELEASES"-table?
Can I somehow use game_ID and platform together to create the primary-key? If so, how would the SQL look like?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以创建一个由
game_id和platform组成的复合键，就像创建仅包含一列的主键一样：You can create a composite key that consists of
game_idandplatformjust like you create a primary key that consists of only one column:您不希望 game_ID 成为 Releases 表中的主键和外键。这将防止表中的每场比赛有多个记录，因为主键必须是唯一的。我会推荐这样的结构。
release_ID 将自动生成。您可以通过在主键中包含平台来使用复合键，但如果单个游戏/平台有多个版本，则可能会遇到问题。您现在可能认为这是不可能的，但情况发生了变化。
我还认为永远不要使用具有任何含义的列作为键是一种很好的做法，因为事情会发生变化，并且您无法预测它们将如何变化。如果您的密钥对最终用户毫无意义，那么他们就不会弄乱您的数据库结构。
You don't want game_ID to be the primary and foreign key in the Releases table. That would prevent the table from having more than one record for each game, since the primary key must be unique. I would recommend a structure like this.
release_ID would be auto-generated. You could use a composite key by including platform in the primary key, but you may run into a problem if a single game/platform has multiple releases. You may not think that's possible now, but things change.
I also consider it good practice to never use a column that has any meaning as a key, since things change and you can't predict how they will change. If you keys are meaningless to end users, then they can't mess with the structure of your database.
首先，考虑将平台实现为单独的查找表。这不仅可以减少数据损坏的可能性，而且还可以使您的密钥定义更加容易。
主键不必仅限于一个字段。您可以使用多个字段定义复合主键。
但是，在这种情况下，我建议不要使用复合主键，并提倡在 Releases 表上创建新的主键。
为什么？好吧，对于初学者来说，我认为您在发布中没有捕获足够的信息。例如，视频游戏通常在不同地区的不同时间发布，因此同一游戏和平台发布两个版本可能是完全有效的。在这种情况下，您需要一个由三个字段组成的复合主键。它在哪里结束？ :)
通过将 ReleaseID 定义为代理主键，您可以为自己在未来提供更多的更改空间。
First, consider realising Platforms as a separate lookup table. Not only does this reduce the chance of data corruption, but it'll also make your key definition easier.
A primary key doesn't have to be restricted to one field. You can define a composite primary key using multiple fields.
However, in this instance, I'd recommend against going the composite primary key, and would advocate the creation of a new primary key on the Releases table.
Why? Well, for starters, I don't think you've captured enough info in Releases. For example, video games are often released at different times in different regions, so it could be perfectly valid to have two releases for the same game and platform. In that circumstance, you'd need a composite primary key comprising of three fields. Where does it end? :)
By defining ReleaseID as a surrogate primary key, you give yourself a lot more scope for change in the future.