Sql 简明教程

SQL - Composite Key

The SQL Composite Key

SQL Composite Key 是可以在表中两个或更多列上定义的关键，用于唯一标识任何记录。它还可以描述为在多个列上创建的主键。

An SQL Composite Key is a key that can be defined on two or more columns in a table to uniquely identify any record. It can also be described as a Primary Key created on multiple columns.

在数据库表没有可以唯一标识表中每一行的单个列的情况下，组合键是必需的。在这种情况下，我们可能需要使用列的组合来确保表中的每条记录都是不同的，并且是可识别的。

Composite Keys are necessary in scenarios where a database table does not have a single column that can uniquely identify each row from the table. In such cases, we might need to use the combination of columns to ensure that each record in the table is distinct and identifiable.

让我们用一个例子来理解组合键。假设我们有一个名为 CUSTOMERS 的表，它具有各种字段，如 ID、NAME、AGE、AADHAAR_ID、MOBILE_NO 和 SALARY，如下所示 −

Let us understand the composite keys with an example. Suppose if we have a table named CUSTOMERS with various fields like ID, NAME, AGE, AADHAAR_ID, MOBILE_NO and SALARY as shown below −

我们可以选择两列 AADHAAR_ID 和 MOBILE_NO，并在它们上定义一个组合键，它可以用于唯一获取 CUSTOMERS 表的记录。

We can select the two columns AADHAAR_ID and MOBILE_NO and define a Composite key on them, and it can be used to fetch the records of the CUSTOMERS table uniquely.

Features of Composite Keys

以下是 SQL 组合键的一些重要特性 −

Following are some important features of the SQL Composite Key −

A Composite Key can be created by combining more than one Candidate Key.
Each Candidate Key (or column) that makes up a Composite Key may or may not be a Foreign Key. However, if all the columns of the Composite Key are Foreign Keys in their own right, then the Composite Key is known as a Compound Key.
A Composite Key cannot be NULL; i.e. any column of the Composite Key must not contain NULL values.
The individual columns making up the Composite Key can contain duplicate values, but, the combination of these columns must be unique across the database table.

Syntax

以下是创建表时创建 SQL 组合键的语法 −

Following is the syntax to create an SQL Composite Key while creating a table −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   CONSTRAINT composite_key_name,
   PRIMARY KEY(column_name)
);

此处， composite_key_name 是用于容纳表中组合键名称的可选占位符。在某些数据库中放弃表中的约束时使用它。

Here, the composite_key_name is the optional placeholder which holds the name of a Composite Key in a table. It is used while dropping the constraint from a table in some databases.

Example

在以下示例中，我们要创建一张具有多个列的 CUSTOMERS 表。当同时在 ID 和 NAME 列上定义 PRIMARY KEY 时，就会创建组合键。查看以下查询：

In the following example, we are creating a table named CUSTOMERS with multiple columns. The Composite Key is created when a PRIMARY KEY is defined on ID and NAME columns together. Look at the query below −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   CONSTRAINT ck_customers
   PRIMARY KEY (ID, NAME)
);

其中，ck_customers 是此表组合键的名称。

Where, ck_customers is the name of a composite key of this table.

Output

以下是上述语句的输出 −

Following is the output of the above statement −

Query OK, 0 rows affected (0.02 sec)

Verification

由于我们在 CUSTOMERS 表的 ID 和 NAME 列上创建了组合键，因此这些列值无法重复。为了验证它，让我们在 CUSTOMERS 表中插入两条在这些列中具有相同值的记录：

As we have created a Composite Key on the columns ID and NAME of the CUSTOMERS table, the combination of values in these columns can not be duplicated. To verify it, let us insert two records with same values in these columns into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(1, 'Ramesh', 25, 'Delhi', 1500.00 );

您可以看到，第二个 INSERT 语句会生成如下所示的错误消息 "Duplicate entry" ：

You can observe that the second INSERT statement generates an error message saying "Duplicate entry" as shown below −

ERROR 1062 (23000): Duplicate entry '1-Ramesh' for key 'customers.PRIMARY'

Dropping a Composite Key in MySQL

可以使用 ALTER TABLE… DROP 语句从 MySQL 数据库中的表中删除组合键。

You can drop the composite key from a table in MySQL database using the ALTER TABLE… DROP statement.

Syntax

以下是 MySQL 中删除组合键的语法：

Following is the syntax to drop the Composite Key in MySQL −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

可以使用以下 SQL 语句从 CUSTOMERS 表中删除 Composite Key 约束：

Using the following SQL statement, we can drop the Composite Key constraint from the CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

以上 SQL 语句产生以下输出：

The above SQL statement produces the following output −

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verification

由于我们已经从 CUSTOMERS 表中删除了组合键，因此现在您可以在 ID 和 NAME 列中插入重复的值。

Since, we have dropped the composite from the CUSTOMERS table, so now you can insert the duplicate values in the columns ID and NAME.

让我们在 CUSTOMERS 表中插入两条具有相同 ID 和 NAME 的记录：

Let us insert two records with the same ID and NAME into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 25, 'Delhi', 1500.00 ),
(1, 'Ramesh', 23, 'Kota', 2000.00 );

如果您检索 CUSTOMERS 表的内容，则可以找到 ID 和 NAME 相同的记录，如下所示：

If you retrieve the contents the CUSTOMERS table you can find the records with same ID and NAME as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
1	Ramesh	25	Delhi	1500.00
1	Ramesh	23	Kota	2000.00

Dropping a Composite Key in SQL Server

在 SQL Server 中，我们有用于删除表组合键的不同语法。语法几乎相似，但我们需要指定组合键名称才能删除它，而不是 PRIMARY KEY 关键字。

In SQL Server, we have a different syntax to drop a composite key of a table. The syntax is almost similar, but we just need to specify the composite key name in order to drop it, rather than the keyword PRIMARY KEY.

Syntax

以下是 SQL Server 中删除组合键的语法：

Following is the syntax to drop a composite key in SQL Server −

ALTER TABLE table_name DROP composite_key_name;

Example

假设在 CUSTOMERS 表的 ID 和 NAME 列上创建了一个组合键 "ck_customers"，我们将使用以下查询删除它：

Assuming that a composite key "ck_customers" is created on ID and NAME columns of the CUSTOMERS table, we will use the following query to drop it −

ALTER TABLE CUSTOMERS DROP ck_customers;

Output

当我们执行上述查询时，将删除组合键。

When we execute the above query, the composite key will be dropped.

Commands completed successfully.

Verification

要验证是否已从 CUSTOMERS 表中删除组合键，请使用以下查询向 ID 和 NAME 列中插入重复值：

To verify whether we have removed the composite key from the CUSTOMERS table or not, insert duplicate values into the ID and NAME columns using the following query −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 25, 'Delhi', 1500.00 ),
(1, 'Ramesh', 23, 'Kota', 2000.00 );

正如我们在下表中看到的，两个客户具有相同的 ID 和 NAME：

As we can see in the table below, both the customers have the same ID and NAME −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
1	Ramesh	25	Delhi	1500.00
1	Ramesh	23	Kota	2000.00