Mysql 简明教程

MySQL - Unique Key

Unique Key 在 MySQL 中，如果应用到数据库表格的某个域上，则不允许在该列中插入重复值，即它用于唯一标识表格中的一条记录。

A Unique Key in MySQL, when applied on a certain field of a database table, does not allow duplicate values to be inserted in that column, i.e. it is used to uniquely identify a record in a table.

通常，任何关系数据库都包含存储在多个表格中的大量信息，并且每个表格都保存着大量记录。当我们处理如此庞大的数据量时，就有可能发生冗余（重复记录）。SQL 键是处理此问题的方法。

Usually, any relational database contains a lot of information stored in multiple tables and each table holds a huge number of records. When we are handling such huge amounts of data there is a chance of redundancy (duplicate records). SQL keys are a way to handle this issue.

这个唯一键充当主键约束的替代；因为唯一键和主键都确保了数据库表格中的列的唯一性。

This Unique Key works as an alternative to the Primary Key constraint; as both unique and primary keys assure uniqueness in a column of a database table.

Creating MySQL Unique Key

我们可以使用 UNIQUE 关键字在 MySQL 表格列上创建唯一键，并且它具有以下特征：

We can create a Unique Key on a MySQL table column using the UNIQUE keyword, and it holds the following features −

Even though unique key is similar to the primary key in a table, it can accept a single NULL value unlike the primary key.
It cannot have duplicate values.
It can also be used as a foreign key in another table.
A table can have more than one Unique column.

Syntax

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

Following is the syntax to create a UNIQUE key constraint on a column in a table −

CREATE TABLE table_name(
   column_name1 datatype UNIQUE,
   column_name2 datatype,
   ...
);

如您所观察到的，我们只需在使用 CREATE TABLE 语句创建表格时，在所需列的名称后面指定关键字 UNIQUE 即可。

As you observe, we just need to specify the keyword UNIQUE after the name of the desired column while creating a table using CREATE TABLE statement.

Example

在这个示例中，让我们创建一个名为 CUSTOMERS 的表格，并在其中一个字段 ADDRESS 上定义唯一键。查看以下查询：

In this example, let us create a table named CUSTOMERS and define a UNIQUE Key on one of its fields, ADDRESS. Look at the following query −

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

Output

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

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

您可以在表格定义中看到，唯一键是在 ADDRESS 字段上创建的。

As you can see in the table definition, the Unique Key is created on the ADDRESS field.

Creating Multiple Unique Keys

我们可以在单个 MySQL 表格中的列上创建一个或多个唯一键约束。当在多个字段中应用此约束时，就不能在这些字段中插入重复值。

We can create one or more Unique Key constraints on a column in a single MySQL table. When this constraint is applied in multiple fields, one cannot insert duplicate values in those fields.

Syntax

以下是在表的多个列上创建唯一键约束的语法：

Following is the syntax to create unique key constraints on multiple columns in a table −

CREATE TABLE table_name(column_name1 UNIQUE, column_name2 UNIQUE,...)

Example

假设我们在 MySQL 数据库中使用 CREATE TABLE 语句创建了另一个名为 CUSTOMERS 的表格。

Assume we have created another table with the name CUSTOMERS in the MySQL database using CREATE TABLE statement.

这里我们使用 UNIQUE 关键字在 NAME 和 ADDRESS 列上创建唯一约束，如下所示：

Here we are creating a UNIQUE constraint on columns NAME and ADDRESS using the UNIQUE keyword as shown below −

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

Output

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

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

Creating Unique Key on Existing Columns

我们可以使用 ALTER TABLE… ADD CONSTRAINT 语句，针对表格现有列添加唯一键约束。

We can add a unique key constraint on an existing column of a table using the ALTER TABLE… ADD CONSTRAINT statement.

Syntax

以下是针对表格现有列创建唯一键的语法：

Following is the syntax to create a UNIQUE Key on existing columns of a table −

ALTER TABLE table_name
ADD CONSTRAINT unique_key_name
UNIQUE (column_name);

Note - 其中 UNIQUE_KEY_NAME 是唯一键的名称。在创建唯一键时，可以不指定名称。此名称用于删除表格中某列的约束。

Note − Here the UNIQUE_KEY_NAME is just the name of the Unique Key. It is optional to specify the name while creating a unique key. It is used to drop the constraint from the column in a table.

Example

使用 ALTER TABLE 语句，可以针对自定义表格中之前的任何现有列添加唯一约束。在以下的示例中，我们对 NAME 列应用唯一约束，如下所示：

Using the ALTER TABLE statement, you can add a UNIQUE constraint on any existing column in the CUSTOMERS table created previously. In the following example, we are applying the UNIQUE constraint on the NAME column as shown below −

ALTER TABLE CUSTOMERS
ADD CONSTRAINT UNIQUE_NAME
UNIQUE (NAME);

Output

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

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

Dropping MySQL Unique Key

如果已有列上的唯一约束，可以随时删除不再需要的约束。要删除表格中某列的唯一约束，需要再次使用 ALTER TABLE 语句。

If there is an unique constraint on a column already, you can drop it whenever it is not needed. To drop the Unique Constraint from the column of a table you need to use the ALTER TABLE statement again.

Syntax

以下是用于从表的列中删除 UNIQUE 约束的 SQL 查询：

Following is the SQL query to drop the UNIQUE constraint from the column of a table −

ALTER TABLE TABLE_NAME DROP CONSTRAINT UNIQUE_KEY_NAME;

Example

在此示例中，我们将使用以下 MySQL 查询，删除表格 CUSTOMERS 中名为 UNIQUE_NAME 的列中 NAME 的约束：

In this example, we will drop the constraint named UNIQUE_NAME from the column NAME of the CUSTOMERS table using the following MySQL query −

ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_NAME;

Output

仅显示的表格结构中包含了 ADDRESS 列的 UNI 索引，表明已删除了 NAME 列的索引。

The table structure displayed will contain a UNI index only on the ADDRESS column, referring that the index on NAME column is removed.

Creating Unique Key Using Client Program

除了使用列上的键通过 MySQL 查询唯一标识，我们还可以通过使用客户端程序，对栏位应用唯一键约束。

In addition to use a key on a column to identify uniquely using the MySQL query We can also apply a Unique Key constraint on a Field using a client program.

Syntax

Example

以下是这些程序 −

Following are the programs −