Mysql 简明教程

MySQL - Primary Key

PRIMARY KEY 是应用于 MySQL 表字段的约束。当应用时，该特定表列中的值唯一标识。它是作为任何表的主键的最合适的候选键。

A PRIMARY KEY is a constraint applied on a field of a MySQL table. When this is applied, the values in that particular table column are uniquely identified. It is the most appropriate candidate key to be the main key of any table.

一个表只能有一个 PRIMARY KEY，其中可能包含单一或多个字段。当使用多个字段作为主键时，它们被称为复合键。

A table can have only one PRIMARY KEY, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a Composite Key.

您可以在创建新表时创建主键，或将其应用到数据库中已存在的表中。但如果将它应用到现有表，则必须确保该表尚未包含主键。

You can either create a primary key while creating a new table or you can apply it on an already existing table in the database. But if it is being applied on an existing table, you must make sure that the table does not already contain a primary key and .

Creating MySQL Primary Key

要在新 MySQL 表上创建主键，必须在使用 CREATE TABLE 语句创建新表时将列指定为 PRIMARY KEY。

To create a primary key on a new MySQL table, you must specify the column as the PRIMARY KEY while creating a new table using the CREATE TABLE statement.

以下是创建表时记住的一些要点 −

Following are some points to remember while creating a Primary Key on a table −

  1. The Primary Key column must only contain unique values.

  2. It can not hold NULL values.

  3. One table can have only one Primary Key.

  4. A Primary Key length cannot be more than 900 bytes.

Syntax

以下是将表格的列定义为主键的语法：

Following is the syntax to define a column of a table as a primary key −

CREATE TABLE table_name(
   column_name NOT NULL PRIMARY KEY(column_name)
);

Example

在以下示例中，让我们使用 CREATE TABLE 查询在 MySQL 数据库中创建一个名为 CUSTOMERS 的表。在此查询中，我们将对名为 ID 的列添加 PRIMARY KEY 约束。

In the following example, let us create a table with the name CUSTOMERS in a MySQL database using the CREATE TABLE query. In this query, we will add the PRIMARY KEY constraint on a column named ID.

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

Output

显示的表结构将在 ADDRESS 列上包含一个 UNI 索引，如下所示 −

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Verification

要进一步验证 PRIMARY KEY 约束是否应用于 ID 列，让我们使用以下查询将不同类型的值插入 CUSTOMERS 表中 −

To verify further that the PRIMARY KEY constraint is applied on the ID column, let us insert different types of values into the CUSTOMERS table using the following queries −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 23, 'Pune', 2000.00),
(1, 'John', 25, 'Hyderabad', 3000.00);

显示了以下错误 −

Following error is displayed −

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

正如我们在上面看到的那样，您不能将重复值和空值插入到此主键列中。

As we can see above, you cannot insert duplicate and null values into this primary key column.

Creating Primary Key on Existing Column

我们还可以对表的现有列添加主键，如果创建新表时出于任何原因未创建主键。但是，仅当表尚未包含主键（因为 MySQL 表不得包含多个主键）且要应用它的列只包含唯一值时，才能对现有表添加主键。

We can also add a primary key on an existing column of a table, if it was not created (for any reason) while creating a new table. However, adding a primary key on an existing table is only possible if the table does not already contain a primary key (as a MySQL table must not contain multiple primary keys), and the column it is being applied on must only contain unique values.

您可以使用 ALTER TABLE…​ ADD CONSTRAINT 语句向现有表中添加主键。

You can add the primary key on an existing table using the ALTER TABLE…​ ADD CONSTRAINT statement.

Syntax

以下是在表的现有列上创建唯一约束的语法：

Following is the syntax to create a unique constraint on existing columns of a table −

ALTER TABLE table_name
ADD CONSTRAINT
PRIMARY KEY (column_name);

Example

使用 ALTER TABLE 语句，您可以在先前创建的 CUSTOMERS 表中的现有列上添加 PRIMARY KEY。在以下示例中，我们对 ID 列应用 PRIMARY KEY，如下所示 −

Using the ALTER TABLE statement, you can add a PRIMARY KEY on an existing column in the CUSTOMERS table created previously. In the following example, we are applying the PRIMARY KEY on the ID column as shown below −

ALTER TABLE CUSTOMERS
ADD CONSTRAINT
PRIMARY KEY (ADDRESS);

Output

显示的表结构将在 ADDRESS 列上包含一个 UNI 索引，如下所示 −

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

但是，如果添加 PRIMARY KEY 的列包含重复值或空值，则不能将其设置为主键。

But if the column, on which the PRIMARY KEY is added, contains duplicate or null values, it cannot be set as a primary key.

Dropping MySQL Primary Key

MySQL 提供了 ALTER TABLE…​ DROP 语句以从表中删除主键。

MySQL provides the ALTER TABLE…​ DROP statement to drop the primary key from a table.

Syntax

以下是使用 ALTER TABLE…​ DROP 语句删除 PRIMARY KEY 约束的语法：

Following is the syntax to drop the PRIMARY KEY constraint using the ALTER TABLE…​ DROP statement −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

让我们考虑具有名为 ID 的列上的主键约束的 CUSTOMERS 表。您可以通过执行以下语句来删除列 ID 上的此约束：

Let us consider the CUSTOMERS table with the primary key constraint present on a column named ID. You can drop this constraint from the column ID by executing the following statement

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

显示的表结构将在 ADDRESS 列上包含一个 UNI 索引，如下所示 −

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Creating Primary Key Using Client Program

我们还可以使用客户端程序对表字段应用主键。

We can also apply a Primary Key on a table field using a client program.

Syntax

Example

以下是这些程序 −

Following are the programs −