Seeing this term (composite foreign key) in the documentation for a framework I am using (yii). What is a composite foreign key (in a mySql database)?
在我正在使用的框架(yii)的文档中看到这个术语(复合外键)。什么是复合外键(在mySql数据库中)?
(My guess is that, given a relationship between two tables, one table has a column with the exact same name as the id the other table.)
(我的猜测是，给定两个表之间的关系，一个表的列与另一个表的id同名。)
*Disclaimer: I did my due diligence and Googled this for like two whole minutes but found no conclusive definition for the term..
*免责声明:我做了尽职调查，用谷歌搜索了大约两分钟，但没有找到这个词的确切定义。
2 个解决方案
#1
6
A composite key consists of more than one attribute to uniquely identify an entity occurrence. This differs from a compound key in that one or more of the attributes, which make up the key, are not simple keys in their own right.
复合键由多个属性组成，以惟一地标识一个实体的出现。这不同于复合键，因为组成键的一个或多个属性本身并不是简单的键。
For example, you have a database holding your CD collection. One of the entities is called tracks, which holds details of the tracks on a CD. This has a composite key of CD name, track number.
例如，您有一个保存CD集合的数据库。其中一个实体叫做磁道，它在CD上保存磁道的细节，它有一个复合键CD名称，磁道号。
CD name in the track entity is a simple key, linking to the CD entity, but track number is not a simple key in its own right.
跟踪实体中的CD名称是一个简单的键，链接到CD实体，但是跟踪号本身并不是一个简单的键。
#2
6
Suppose we have a table of users:
假设我们有一个用户表:
+---------+----------+------------+------------+----------------+ | Surname | Forename | ZIP | DOB | Email | +---------+----------+------------+------------+----------------+ | Jones | John | 60612-0344 | 1970-02-14 | john@jones.com | | Jones | Jane | 60612-0344 | 1971-05-26 | jane@jones.com | | Smith | Sara | 19002-0052 | 1982-06-21 | sara@smith.com | +---------+----------+------------+------------+----------------+
Because our application requires every user to have their own distinct email address, we can uniquely identify records within the table by the value in the Email column: it forms a key into the table. Such keys (defined over a single column) are said to be simple.
因为我们的应用程序要求每个用户都有自己不同的电子邮件地址，所以我们可以通过电子邮件列中的值唯一地标识表中的记录:它将成为表中的一个键。这样的键(在一列上定义)据说很简单。
In some situations, one might know that no two users can have the same name, date of birth and ZIP code: then another possible key would be the combination of (Surname, Forename, ZIP, DOB). Such keys (defined over multiple columns) are said to be composite.
在某些情况下，您可能知道没有两个用户可以拥有相同的名字、出生日期和邮政编码:然后另一个可能的键是(姓、名、ZIP、DOB)的组合。这些键(在多个列上定义)被称为复合键。
Since each record's key must (by definition) be unique to it, one can tell MySQL to enforce such uniqueness constraints by defining a UNIQUE index over the relevant columns (the table's PRIMARY KEY is a special type of UNIQUE index): attempts to create (or update) a record with the same key as an existing record will fail.
因为每个记录的键必须(根据定义)是唯一的,一个可以告诉MySQL来执行这样的唯一性约束通过定义一个唯一的索引列在相关(表的主键是一种特殊类型的惟一索引):试图创建(或更新)与现有的相同的密钥记录将会失败。
Now suppose one has a table of orders:
现在假设有一个命令表:
+--------------+-----------+---------+----------+ | Order_number | Status | Total | Customer | +--------------+-----------+---------+----------+ | 12345 | Completed | 1234.99 | ? | | 12346 | Pending | 345.00 | ? | | 12347 | Cancelled | 9876.50 | ? | +--------------+-----------+---------+----------+
We wish to associate orders with the relevant record from the users table. But how to do that? What do we put in the Customer column?
我们希望将订单与用户表中的相关记录关联起来。但是怎么做呢?我们在客户栏里放什么?
Clearly we wish to identify a unique record in the users table, so we need to use one of its keys (such as Email in the first example above). Using one table's key to reference its records from another table in this manner is extremely commonplace in relational databases: in such situations, we refer to the referencing column as being a foreign key (since it holds keys into a foreign table).
显然，我们希望在users表中标识一个惟一的记录，因此需要使用其中的一个键(如上面第一个示例中的电子邮件)。使用一个表的键以这种方式从另一个表引用它的记录在关系数据库中是非常常见的:在这种情况下，我们将引用列称为外键(因为它将键保存到外表中)。
Should we use a composite key for the reference, we would have a composite foreign key. Under the second example above, our orders table might have columns Customer_Surname, Customer_Forename, Customer_ZIP and Customer_DOB which would together form a foreign key into the users table (in this case, I wouldn't recommend such a schema).
如果我们使用复合键作为引用，我们将有一个复合外键。在上面的第二个示例中，我们的orders表可能包含customer_姓氏、customer_法名、Customer_ZIP和Customer_DOB列，它们将一起构成用户表的外键(在本例中，我不推荐这种模式)。
MySQL can not only enforce foreign key constraints (ensuring that the referenced record exists in the foreign table), but can also automatically update or delete the referencing (orders) table if the referenced record is itself updated or deleted. For example, if John was deleted from the users table, all of his orders could be automatically purged from the orders table (again, probably not what one wants in this case); or if his email address changed, the Customer column could be automatically updated.
MySQL不仅可以强制外键约束(确保外表中存在引用记录)，还可以自动更新或删除引用(order)表，如果引用记录本身被更新或删除。例如，如果从users表中删除了John，那么他的所有订单都可以从orders表中自动清除(在这种情况下，很可能不是我们想要的);或者，如果他的电子邮件地址发生变化，客户栏可以自动更新。
#1
6
A composite key consists of more than one attribute to uniquely identify an entity occurrence. This differs from a compound key in that one or more of the attributes, which make up the key, are not simple keys in their own right.
复合键由多个属性组成，以惟一地标识一个实体的出现。这不同于复合键，因为组成键的一个或多个属性本身并不是简单的键。
For example, you have a database holding your CD collection. One of the entities is called tracks, which holds details of the tracks on a CD. This has a composite key of CD name, track number.
例如，您有一个保存CD集合的数据库。其中一个实体叫做磁道，它在CD上保存磁道的细节，它有一个复合键CD名称，磁道号。
CD name in the track entity is a simple key, linking to the CD entity, but track number is not a simple key in its own right.
跟踪实体中的CD名称是一个简单的键，链接到CD实体，但是跟踪号本身并不是一个简单的键。
#2
6
Suppose we have a table of users:
假设我们有一个用户表:
+---------+----------+------------+------------+----------------+ | Surname | Forename | ZIP | DOB | Email | +---------+----------+------------+------------+----------------+ | Jones | John | 60612-0344 | 1970-02-14 | john@jones.com | | Jones | Jane | 60612-0344 | 1971-05-26 | jane@jones.com | | Smith | Sara | 19002-0052 | 1982-06-21 | sara@smith.com | +---------+----------+------------+------------+----------------+
Because our application requires every user to have their own distinct email address, we can uniquely identify records within the table by the value in the Email column: it forms a key into the table. Such keys (defined over a single column) are said to be simple.
因为我们的应用程序要求每个用户都有自己不同的电子邮件地址，所以我们可以通过电子邮件列中的值唯一地标识表中的记录:它将成为表中的一个键。这样的键(在一列上定义)据说很简单。
In some situations, one might know that no two users can have the same name, date of birth and ZIP code: then another possible key would be the combination of (Surname, Forename, ZIP, DOB). Such keys (defined over multiple columns) are said to be composite.
在某些情况下，您可能知道没有两个用户可以拥有相同的名字、出生日期和邮政编码:然后另一个可能的键是(姓、名、ZIP、DOB)的组合。这些键(在多个列上定义)被称为复合键。
Since each record's key must (by definition) be unique to it, one can tell MySQL to enforce such uniqueness constraints by defining a UNIQUE index over the relevant columns (the table's PRIMARY KEY is a special type of UNIQUE index): attempts to create (or update) a record with the same key as an existing record will fail.
因为每个记录的键必须(根据定义)是唯一的,一个可以告诉MySQL来执行这样的唯一性约束通过定义一个唯一的索引列在相关(表的主键是一种特殊类型的惟一索引):试图创建(或更新)与现有的相同的密钥记录将会失败。
Now suppose one has a table of orders:
现在假设有一个命令表:
+--------------+-----------+---------+----------+ | Order_number | Status | Total | Customer | +--------------+-----------+---------+----------+ | 12345 | Completed | 1234.99 | ? | | 12346 | Pending | 345.00 | ? | | 12347 | Cancelled | 9876.50 | ? | +--------------+-----------+---------+----------+
We wish to associate orders with the relevant record from the users table. But how to do that? What do we put in the Customer column?
我们希望将订单与用户表中的相关记录关联起来。但是怎么做呢?我们在客户栏里放什么?
Clearly we wish to identify a unique record in the users table, so we need to use one of its keys (such as Email in the first example above). Using one table's key to reference its records from another table in this manner is extremely commonplace in relational databases: in such situations, we refer to the referencing column as being a foreign key (since it holds keys into a foreign table).
显然，我们希望在users表中标识一个惟一的记录，因此需要使用其中的一个键(如上面第一个示例中的电子邮件)。使用一个表的键以这种方式从另一个表引用它的记录在关系数据库中是非常常见的:在这种情况下，我们将引用列称为外键(因为它将键保存到外表中)。
Should we use a composite key for the reference, we would have a composite foreign key. Under the second example above, our orders table might have columns Customer_Surname, Customer_Forename, Customer_ZIP and Customer_DOB which would together form a foreign key into the users table (in this case, I wouldn't recommend such a schema).
如果我们使用复合键作为引用，我们将有一个复合外键。在上面的第二个示例中，我们的orders表可能包含customer_姓氏、customer_法名、Customer_ZIP和Customer_DOB列，它们将一起构成用户表的外键(在本例中，我不推荐这种模式)。
MySQL can not only enforce foreign key constraints (ensuring that the referenced record exists in the foreign table), but can also automatically update or delete the referencing (orders) table if the referenced record is itself updated or deleted. For example, if John was deleted from the users table, all of his orders could be automatically purged from the orders table (again, probably not what one wants in this case); or if his email address changed, the Customer column could be automatically updated.
MySQL不仅可以强制外键约束(确保外表中存在引用记录)，还可以自动更新或删除引用(order)表，如果引用记录本身被更新或删除。例如，如果从users表中删除了John，那么他的所有订单都可以从orders表中自动清除(在这种情况下，很可能不是我们想要的);或者，如果他的电子邮件地址发生变化，客户栏可以自动更新。