在什么条件下，我们需要在数据库中使用复合键 [英] Under what condition we need to use composite keys in database

问题描述

我已经看到我们可以有复合键，其中主键由两个表的组合主键组成。

i have seen that we can have composite keys where the primary key is made up from combined primary keys of two tables.

喜欢人物和书籍

person_id and book_id will make the primary key.

但我想问我们有需要硬编码的程序langauge

But i want to ask that we there is need to hard code that programming langauge

我的意思是确定我可以有任何名称的单独列作为主键
然后我不必硬编码它，我可以执行我的功能，正常如

I mean is it ok i can have separate column with any name to work as primary key then i don't have to hard code it and i can perform my functions as normal like

id,person_id ,book_id

推荐答案

在新应用程序中不应考虑复合键。它们在过去被曾经被认为商务密钥比代理密钥更好的人使用。

Composite keys should never be considered in "new" applications. They were used in the past, by people who used to think that "business keys" are better than "surrogate keys".

编辑：按照Chris的要求，我会扩大我的答案。

As asked by Chris, I'm expanding my answer.

我理解这个问题为复合主键与代理键。

Let me start by stating that I understand this question as "Composite Primary Keys" vs. "Surrogate keys".

此外，我承认有一个用例，其中复合键是有意义的：在交叉引用表中，也称为链接表 。这些在多对多表中使用，并且只包含两个字段，两个外键都构成了外部参照表的主键。例如， UserRole 表将包含 user_id 和 role_id 没有其他的。在Java中没有类表示，例如，对于这样的表。这通常是 @ManyToMany ，两边都有集合。

Also, I concede that there is one use case where a composite key makes sense: in cross reference tables, also called "link tables". These are used in many-to-many tables and consists in only two fields, both foreign keys that form a primary key for the xref table. For instance, UserRole table would contain user_id and role_id, nothing else. There's no class representation in Java, for instance, for a table like this. This is usually a @ManyToMany, with a Collection in both sides.

我在另一个答案（ Hibernate：Composite PK vs Surrogate PK中的意见），我认为复合键共享Natural Key的一些缺点，没有带来任何实际的好处。

I shared my views on Natural keys vs. Surrogate keys in another answer ( Hibernate : Opinions in Composite PK vs Surrogate PK ) , and I believe that Composite keys shares some of the disadvantages of the Natural key, without bringing any real benefit.

复合键的问题是您需要两个值来唯一标识记录。一旦您开始使用引用此第一个表中的记录的表，这就成为一个问题。第二个表格需要两个列才能参考一个记录。如果此第二个表格使用由单个值+外键组成的复合键，那么现在您有三个列来唯一标识一个记录。第三个表需要这些三个额外列，只是为了引用第二个表中的一个记录。真的，这是一个雪球。

The problem with composite keys is that you'll need two values to uniquely identify a record. This becomes a problem once you start having tables which references records in this first table. The second table then needs two columns to be able to reference one record. And if this second table uses a composite key made up of a single value + the foreign key, you now have three columns to uniquely identify one record. And a third table would need these three extra columns just to reference one record in the second table. Really, this is a snow ball.

另一个缺点是要求做更改。每时每刻。所以，今天似乎是一个好的复合钥匙不是明天的钥匙。这就是为什么我们有代理键：要面向未来。

Another disadvantage is that requirements do change. All the time. So, what seems to be a good composite key today is not a key at all tomorrow. That's why we have surrogate keys: to be future-proof.

复合键主要用于使表中的记录基于一组列是唯一的。例如，如果您有 Customers 表，您可能有 NationalId + / code>作为唯一值，这意味着如果两个用户的国家是美国，则两个用户不能共享同一个SSN。但是，如果两个记录不在同一个国家/地区，则可能有相同的数字。如果你喜欢复合键，这将是一个很好的候选人。但是正如我之前提到的，您可以使用代理键，并应用唯一约束。您将拥有复合键的好处以及代理键的安全性。

Composite keys are mainly used so that records in a table are unique based on a set of columns. For instance, if you have a Customers table, you may have a NationalId+Country as a unique value, meaning that two users cannot share the same SSN if their country is USA. But it's possible to have the same number for two records, if they are not in the same country. If you like composite keys, this would be a good candidate for it. But as I hinted earlier, you can use a surrogate key and apply a unique constraint. You'll have the benefits of a composite key plus the safety of a surrogate key.

这篇关于在什么条件下，我们需要在数据库中使用复合键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！