MySQL 中的复合主键示例

Composite Primary Keys example in MySQL

提问by user1686230

I have encountered MySQL itself recently and the topic of Composite Primary Keys in MySQL, especially how it is useful and what are its pros and cons from this site

我曾经遇到过的MySQL本身最近和复合主键在MySQL中，尤其是如何是有用的，的话题什么是它的利弊，从本网站

I wanted to play with that, so I have created three tables in this fashion:

我想玩这个，所以我以这种方式创建了三个表：

CREATE TABLE person(
    personId INT(11) NOT NULL,
    personName VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId)
)

CREATE TABLE language(
    languageId INT(11) NOT NULL,
    languageName VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId)
)

CREATE TABLE personLanguage(
    personId INT(11) NOT NULL,
    languageId INT(11) NOT NULL,
    description VARCHAR(20) NOT NULL,
    PRIMARY KEY(personId, languageId),
    FOREIGN KEY (personId) REFERENCES person(personId) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (languageId) REFERENCES language(languageId) ON UPDATE CASCADE ON DELETE CASCADE
)

I can insert data into the person and language tables-straight forward, my questions are:

我可以直接将数据插入到 person 和 language 表中，我的问题是：

1. For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

2. Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

3. How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

1. 对于 personLanguage 表，我是否只需要插入 description 列，而其他列会被自动引用，或者我是否需要在 personLanguage 表中插入另外两列的值

2. 是否有可能在插入其他两个表中的数据后立即自动更新 personLanguage 表中的 personId 和 languageId，据我所知，当在 person 或 language 表中进行某些更新/删除时，它反映相同personLanguage 表中的两列

3. 如何获取与三个表相关的数据，例如我需要知道personId=1的人说哪种语言？它也是使用连接的直接查询还是有其他方法可以做，因为我使用复合主键

Lots of questions bugging my mind and I could not really find a whole working example to check the exact pros and cons of using composite primary keys. In case if somebody could elaborate this using my example, would be really helpful.

很多问题困扰着我，我找不到一个完整的工作示例来检查使用复合主键的确切优缺点。如果有人可以使用我的示例详细说明这一点，那将非常有帮助。

I know I have sort of asked some basic, some what makes no sense question, but please do bear me and throw some good light on this topic

我知道我问了一些基本的，一些没有意义的问题，但请耐心等待，并就这个话题提出一些好的看法

采纳答案by Brian Hoover

For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

对于 personLanguage 表，我是否只需要插入 description 列，而其他列会被自动引用，或者我是否需要在 personLanguage 表中插入另外两列的值

Yes, you will need to insert all three of the columns to be completely valid. Otherwise the DB won't know what person or language you are trying to tie this record to.

是的，您需要插入所有三列才能完全有效。否则 DB 将不知道您试图将此记录与什么人或语言联系起来。

Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

是否有可能在插入其他两个表中的数据后立即自动更新 personLanguage 表中的 personId 和 languageId，据我所知，当在 person 或 language 表中进行某些更新/删除时，它反映相同personLanguage 表中的两列

You could do this via an insert trigger, but it might not make any sense. So, let's say that you just entered a new language - say French. You shouldn't need to enter any values at all into the personLanguage table because your existing users might not want to get information in French. The same situation would be for creating a new person. You might have many languages. Most people won't speak most of the languages, so again, you wouldn't want to enter a record into the personLanguage table automatically.

您可以通过插入触发器执行此操作，但这可能没有任何意义。因此，假设您刚刚输入了一种新语言 - 比如说法语。您根本不需要在 personLanguage 表中输入任何值，因为您现有的用户可能不想获得法语信息。同样的情况也适用于创建一个新人。您可能有多种语言。大多数人不会说大多数语言，因此，您也不希望自动将记录输入到 personLanguage 表中。

As for updating the records in person and language, the KEYS shouldn't change. This is why you would do something like this. Once Bob or Alice is assigned a personId, they are that Id. Once French is assigned a langaugeId, it should always be that languageId.

至于更新个人和语言记录，KEYS 应该不会改变。这就是为什么你会做这样的事情。一旦 Bob 或 Alice 被分配了一个 personId，他们就是那个 Id。一旦法语被分配了一个 langaugeId，它应该始终是那个 languageId。

How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

如何获取与三个表相关的数据，例如我需要知道personId=1的人说哪种语言？它也是使用连接的直接查询还是有其他方法可以做，因为我使用复合主键

Well, this is the tricky question. If you are trying to get ALL the languages personId=1 speaks, the join is pretty easy.

嗯，这是一个棘手的问题。如果您试图获取 personId=1 所说的所有语言，则加入非常简单。

select pl.personId, l.languageId, l.languageName 
  from personLanguage pl
    join language l on l.languageId = pl.languageId
    where pl.personId = 1

It gets more complicated if you are trying to figure out which language you should communicate with the person, since there is a chance that the person might not have any personLanguages defined. If you can accept null values, you can use an outer join, but you would want to define the query so that you only return a single language.

如果您试图确定应该与此人交流的语言，则情况会变得更加复杂，因为此人可能没有定义任何 personLanguages。如果您可以接受空值，则可以使用外部联接，但您可能希望定义查询以便仅返回一种语言。

回答by Kermit

1. For the personLanguage table do I need to insert only description column, while the other columns are automatically referenced, or do I need to insert the values for the other two columns in personLanguage table as well

   You can only insert values into personLanguageif there are existing keys in the referenced tables. This means you must populate personand languageprior to inserting values into personLanguage. However, if you have a NULLable field, you could do this, but it would violate the unique composite key.

2. Is there a possibility to update the personId and languageId in personLanguage table automatically as soon as the data in other two tables are inserted, as far as I know when some update/delete is done in either of person or language tables it reflects the same on the two columns in personLanguage table

   The constraint that you have specified (ON UPDATE CASCADE) means that when there is a change to a referenced value in either personor languageit will automatically update those values in personLanguage. However, there cannot be a violation of of the PRIMARY KEYconstraint on personLanguage.

3. How to fetch the data relating the three tables, for example I need to know which language does the person with personId=1 speaks? Is it also straight forward query using joins or is there some other way to do since I use composite primary keys

   Since this is a basic example, there wouldn't really be a need for this. In an extended form, you could use explicit JOINs to fetch data between the tables.

1. 对于 personLanguage 表，我是否只需要插入 description 列，而其他列会被自动引用，或者我是否需要在 personLanguage 表中插入另外两列的值

   personLanguage如果引用的表中存在现有键，则只能将值插入其中。这意味着你必须填写person与language前值插入personLanguage。但是，如果您有一个NULL能够字段，您可以这样做，但它会违反唯一组合键。

2. 是否有可能在插入其他两个表中的数据后立即自动更新 personLanguage 表中的 personId 和 languageId，据我所知，当在 person 或 language 表中进行某些更新/删除时，它反映相同personLanguage 表中的两列

   您已指定（约束ON UPDATE CASCADE），当发现有一个变化在任何一个参考价值person或者language它会自动在更新这些值personLanguage。但是，不能违反 上的PRIMARY KEY约束personLanguage。

3. 如何获取与三个表相关的数据，例如我需要知道personId=1的人说哪种语言？它也是使用连接的直接查询还是有其他方法可以做，因为我使用复合主键

   由于这是一个基本示例，因此实际上不需要这样做。在扩展形式中，您可以使用显式JOINs 在表之间获取数据。

Just a few more thoughts...

还有一些想法......

Composite keys are generally used for referencing in tuples (or sets). This means that when you have a composite key (col1, col2) on table1, this references a composite key (col1, col2) on table2.

复合键通常用于在元组（或集合）中进行引用。这意味着当你有一个复合键 ( col1, col2) on 时table1， this 引用一个复合键 ( col1, col2) on table2。