Mysql 简明教程
MySQL - Composite Key
MySQL Composite Key 是由表中两列或更多列组成的键，用于唯一标识记录（同一表行中值的组合）。它还可以描述为在多个列上创建的主键。
A MySQL Composite Key is a key that consists of two or more columns in a table, used to uniquely identify a record (combination of values in the same table row). It can also be described as a Primary Key created on multiple columns.
对于表中多列上的复合键，这些列的组合可以保证唯一性，即使这些列单独可能无法保证唯一性。因此，当数据库表没有任何列能够单独从表中标识唯一行（或记录）时，我们可能需要两个或更多个字段/列才能从表中获取唯一记录/行。
With composite key on multiple columns of a table, a combination of these columns guarantees uniqueness, even though individually these columns may or may not guarantee uniqueness. Therefore, when the database table doesn’t have any column which is individually capable of identifying a unique row (or a record) from the table, then we might need two or more two fields/columns to get a unique record/row from the table.
Creating MySQL Composite Key
要在 MySQL 表中创建复合键，我们在 CREATE TABLE 语句中使用 PRIMARY KEY 关键字在表的两个或更多列上创建主键。复合键必须具有以下特性 -
To create a composite key in a MySQL table, we create a primary key on two or more columns of a table using the PRIMARY KEY keyword in the CREATE TABLE statement. The composite key must have the following features −
-
A Composite Key may or may not be a part of the Foreign key.
-
A Composite Key can not be NULL.
-
A Composite Key also can be created by combining more than one Candidate Key.
-
It is also known as Compound key.
-
All the attributes in a compound keys are foreign keys.
Syntax
以下是创建表时创建复合键的语法 -
Following is the syntax to create a Composite Key while creating a table −
CREATE TABLE table_name(
column1 datatype, column2 datatype, column3 datatype...,
CONSTRAINT composite_key_name
PRIMARY KEY(column_name1, column_name2,..)
);Example
在以下示例中，我们尝试创建一个名为 CUSTOMERS 的表，并在 ID 和 NAME 列上添加一个复合键，如下所示 -
In the following example, we are trying to create a table named CUSTOMERS and add a composite key on ID and NAME columns as shown −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY(ID, NAME)
);PRIMARY KEY 添加到 CUSTOMERS 表中的 ID 和 NAME 列。插入到这些列中的值的组合必须是唯一的，即使单个列值有重复项。
PRIMARY KEY is added to both ID and NAME columns in the CUSTOMERS table. The combination of values inserted into these columns must be unique, even if the individual column values has duplicates.
Dropping MySQL Composite Key
我们可以使用 ALTER TABLE…DROP 语句删除 MySQL 复合键。
We can drop the MySQL Composite Key by using the ALTER TABLE… DROP statement.
Syntax
以下是删除表中列的复合键的语法 -
Following is the syntax to drop the Composite key from the column of a table −
ALTER TABLE table_name DROP PRIMARY KEY;