我应该使用复合主键吗？ [英] Should I use composite primary keys or not?
问题描述
我找到一个同意我的人：
http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx
但另一个没有：
http://weblogs.java.net/blog/bleonard/archive/2006/11/using_composite.html
是只是我，还是人们不能区分复合键是否合适？当表不表示一个实体时，我看到复合主键是有用的 - 即表示一个连接表时。
一个简单的例子：
演员{Id，Name，Email}
电影{Id，Name，Year}
字符{Id，Name}
角色{演员，电影，角色}
这里，演员，电影和角色显然可以从Id列中获益主键。
但角色是一个多对多连接表。创建一个id只是为了识别数据库中的一行，我看不出任何意义。对我来说，显然主键是 {Actor，Movie，Character} 。这似乎是一个相当有限的功能，特别是如果连接表中的数据一直发生变化，那么一旦主键序列绕成0，就可以发现主键碰撞。
所以，回到原来的问题，使用复合主键还是可以接受的做法吗？如果没有，为什么不这样？
我认为使用复合键没有问题。
对我来说，这个数据库是一个独立的组件，它们应该与处理代码的方式一样：例如我们需要清理代码，清楚地表达其意图，这样做有一件事情，这不会增加任何不合适的复杂程度等等。
与db相同的事情，如果PK是复合的，这是现实，所以模型应该是保持清洁。复合PK比混合自动增量+约束更清晰。当你看到一个ID列不做任何事情你需要问什么是真正的PK，有什么其他隐藏的东西，你应该知道等等。一个明确的PK不会留下任何疑问。
数据库是应用程序的基础，对我来说，我们需要我们可以拥有的最坚实的基础。在这个基础上，我们将构建应用程序（网络或不是）。所以我不明白为什么我们应该弯曲数据库模型以符合一个开发工具/框架/语言中的一些特定的。数据是指导应用程序，而不是其他方式。如果ORM在未来发生变化并变得过时了，会出现更好的解决方案，强加另一种模式？我们无法使用数据库模型来适应这个或者那个框架，模型应该保持不变，它不应该依赖于我们用来访问数据的工具...
如果数据库模型在将来发生变化，则应改变功能，因为功能已更改。如果我们今天知道这个功能将如何改变，我们将会对此进行建模。任何未来的变化将在时间到来时得到处理，我们无法预测对现有数据的影响，因此，一个额外的列不能保证将来会改变任何未来的变化。
我们应该设计今天的功能，并保持数据库模型的最简单可能，这样可以在将来轻松改变/演变。
There seems to only be 2nd class support for composite database keys in Java's JPA (via EmbeddedId or IdClass annotations). And when I read up on composite keys, regardless of language, people keep coming across as them being a bad thing. But I cannot understand why. Are composite keys still acceptable to use these days? If not, why not?
I've found one person who agrees with me: http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx
But another who doesn't: http://weblogs.java.net/blog/bleonard/archive/2006/11/using_composite.html
Is it just me, or are people not able to make the distinction of where a composite key is appropriate or not? I see composite primary keys useful when the table doesn't represent an entity - i.e. when it represents a join table.
A simple example:
Actor { Id, Name, Email }
Movie { Id, Name, Year }
Character { Id, Name }
Role { Actor, Movie, Character }
Here Actor, Movie and Character obviously benefit from having an Id column as the primary key.
But Role is a Many-To-Many join table. I see no point in creating an id just to identify a row in the database. To me it seems obvious that the primary key is { Actor, Movie, Character }. It also seems like a rather limiting feature, especially if the data in the join table changes all the time, you could find yourself with primary key collisions once the primary key sequence wraps around to 0.
So, back to the original question, is it still acceptable practice to use composite primary keys? If not, why not?
I think there's no problem using a composite key.
To me the database it's a component on its own, that should be treated the same way we treat code : for instance we want clean code, that communicates clearly its intent, that does one thing and does it well, that doesn't add any uneeded level of complexity, etc.
Same thing with the db, if the PK is composite, this is the reality, so the model should be kept clean and clear. A composite PK it's clearer than the mix auto-increment + constraint. When you see an ID column that does nothing you need to ask what's the real PK, are there any other hidden things that you should be aware of, etc. A clear PK doesn't leave any doubts.
The db is the base of your app, to me we need the most solid base that we can have. On this base we'll build the app ( web or not ). So I can't see why we should bend the db model to conform to some specific in one development tool/framework/language. The data is directing the application, not the other way around. What if the ORM changes in the future and becomes obsolete and a better solution appears that imposes another model ? We can't play with the db model to fit this or that framework, the model should stay the same, it should not depend on what tool we're using to access the data ...
If the db model change in the future, it should change because functionality changed. If we would know today how this functionality will change, we'll be modeling this already. ANd any future change will be dealt with when the time comes, we can't predict for instance the impact on existing data, so one extra column doesn't guarantee that it will withold any future change ...
We should design for today's functionality, and keep the db model the simplest possible, this way it will be easy to change/evolve in the future.
这篇关于我应该使用复合主键吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
