Sql 简明教程

SQL - Unique Key

The SQL Unique Key

SQL Unique Key （或，唯一约束）不允许表中的列有重复的值。它防止两条记录在某一列上具有相同的值。

The SQL Unique Key (or, Unique constraint) does not allow duplicate values in a column of a table. It prevents two records from having same values in a column.

假设我们有一个名为 CUSTOMERS 的表，用于存储银行中的客户记录，如果列名称之一为 MOBILE_NO，那么可在此列上创建唯一约束，以防止输入多个具有相同移动号码的记录。

Suppose we have a table named CUSTOMERS to store the customer records in a Bank and if one of the column names is MOBILE_NO then, we can create a UNIQUE constraint on this column to prevent the entry of multiple records with the same mobile number.

Features of Unique Keys

以下是 SQL 数据库中唯一键的一些关键特性列表：

Following is the list of some key features of the Unique Key in an SQL database −

  1. The unique key is similar to the primary key in a table, but it can accept NULL values, whereas the primary key does not.

  2. It accepts only one NULL value.

  3. It cannot have duplicate values.

  4. It can also be used as a foreign key in another table.

  5. A table can have more than one Unique column.

Creating SQL Unique Key

可以使用 SQL 中的 UNIQUE 关键字在数据库表上创建唯一键。在创建数据库表时，指定此 SQL 关键字以及要在此列中定义键的列。

You can create a Unique Key on a database table using the UNIQUE keyword in SQL. While creating a database table, specify this SQL keyword along with the column (where this key needs to be defined on).

Syntax

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

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

CREATE TABLE table_name(
   column1 datatype UNIQUE KEY,
   column2 datatype,
   .....
   .....
   columnN datatype
);

Example

使用以下 SQL 查询，我们在其中创建了一个名为 CUSTOMERS 的表，其中有五个字段：ID、NAME、AGE、ADDRESS 和 SALARY。在此，我们在 ID 列上创建了唯一键。

Using the following SQL query, we are creating a table named CUSTOMERS with five fields ID, NAME, AGE, ADDRESS, and SALARY in it. Here, we are creating a Unique Key on the ID column.

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

Output

以下是上述 SQL 语句的输出：

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.03 sec)

Verification

由于我们在名为 ID 的列上创建了唯一约束，因此我们不能在其中插入重复的值。让我们通过向 CUSTOMERS 表中插入具有重复 ID 值的以下记录进行验证：

Since we have created a UNIQUE constraint on the column named ID, we cannot insert duplicate values in it. Let us verify by inserting the following records with duplicate ID values into the CUSTOMERS table −

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

执行后，显示了以下错误，证明 UNIQUE 约束确实在 ID 列中定义：

On execution, following error is displayed proving that the UNIQUE constraint is indeed defined on the ID column −

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

Multiple Unique Keys

我们可以在 SQL 表中的一个或多个列上创建唯一键。

We can create one or more Unique Keys on one or more columns in an SQL table.

Syntax

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

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

CREATE TABLE table_name(
   column1 datatype UNIQUE KEY,
   column2 datatype UNIQUE KEY,
   .....
   .....
   columnN datatype
);

Example

假设我们在 SQL 数据库中使用 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表。使用 UNIQUE 关键字在 ID 和 NAME 列上定义唯一键，如下所示：

Assume we have created a table with the name CUSTOMERS in the SQL database using CREATE TABLE statement. A Unique key is defined on columns ID and NAME using the UNIQUE keyword as shown below −

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

Output

以下是上述 SQL 语句的输出：

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.03 sec)

Verification

由于我们在名为 ID 和 NAME 的列上创建了 UNIQUE 约束，因此我们不能向其插入重复值。让我们通过使用以下 INSERT 语句向 BUYERS 表中插入重复记录来验证：

Since we have created a UNIQUE constraint on the column named ID and NAME, we cannot insert duplicate values in it. Let us verify by inserting duplicate records into the BUYERS table using the following INSERT statement −

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

显示了以下错误 −

Following error is displayed −

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

同样，如果你尝试为 NAME 列插入另一个重复值记录，则为：

In the same way if you try to insert the another record with duplicate value for the column NAME as −

INSERT INTO BUYERS VALUES (2, 'Ramesh', 36, 'Chennai', 1700.00 );

生成以下错误：

Following error is generated −

ERROR 1062 (23000): Duplicate entry 'Ramesh' for key 'buyers.NAME'

Unique Key on an Existing Column

到目前为止，我们只看到如何在创建新表时为列定义唯一键。但是，我们还可以在表的现有列上添加唯一键。这通过 ALTER TABLE…​ ADD CONSTRAINT 语句完成。

Until now, we have only seen how to define a Unique Key on a column while creating a new table. But, we can also add a unique key on an existing column of a table. This is done 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
UNIQUE_KEY_NAME UNIQUE (column_name);

Note - 在此处，UNIQUE_KEY_NAME 只是 UNIQUE KEY 的名称。指定它是可选的，用于从表中的列中删除约束。

Note − Here the UNIQUE_KEY_NAME is just the name of the UNIQUE KEY. It is optional to specify and is used to drop the constraint from the column in a table.

Example

在此示例中，我们向现有 CUSTOMERS 表的 ADDRESS 列添加唯一键：

In this example, we add a Unique Key on the ADDRESS column of the existing CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD CONSTRAINT
UNIQUE_ADDRESS UNIQUE(ADDRESS);

Output

以下是上述语句的输出 −

Following is the output of the above statement −

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Dropping an SQL Unique Key

如果你已在列上创建唯一键，则可在任何不需要时将其删除。要从表的列中删除唯一键，你需要使用 ALTER TABLE 语句。

If you have already created a unique key on a column, you can drop it whenever it is not needed. To drop the Unique Key from the column of a table, you need to use the ALTER TABLE statement.

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

考察上面创建的 CUSTOMERS 表，我们在名为 ID、NAME 和 ADDRESS 的三列上创建了 UNIQUE 约束；通过执行以下 SQL 查询，从 ADDRESS 列中删除 UNIQUE 约束：

Consider the CUSTOMERS table created above, we have created the UNIQUE constraints on three columns named ID, NAME and ADDRESS; drop the UNIQUE constraints from the column ADDRESS by executing the following SQL query −

ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_ADDRESS;

Output

以下是上述语句的输出 −

Following is the output of the above statement −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

现在，让我们插入 ADDRESS 列的两个重复记录：

Now, let us insert two duplicate records of column ADDRESS −

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

如果你验证表中的内容，你可以观察到两个记录都有相同的 ADDRESS，如下所示：

If you verify the contents of the table, you can observe that both the records have the same ADDRESS as shown below −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Ahmedabad

1500.00