MySQL UNIQUE约束

时间:2019-11-20 08:52:12  来源:igfitidea点击:

简介:在本教程中,您将学习MySQL UNIQUE约束以及如何使用UNIQUE约束来强制表中一列或一组列中值的唯一性。

MySQL UNIQUE约束简介

有时,您想确保一列或一组列中的值是唯一的。
例如,用户表中用户的电子邮件地址或客户表中客户的电话号码应唯一。
要强制执行此规则,请使用UNIQUE约束。

UNIQUE约束是一个完整性约束,可确保一列或一组列中的值唯一。
UNIQUE约束可以是列约束或表约束。

要在创建表时为列定义UNIQUE约束,请使用以下语法:

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

在这种语法中,您要在要实施唯一性规则的列的定义中包括UNIQUE关键字。
如果您插入或更新导致column_name中重复的值,则MySQL拒绝更改并发出错误。

此UNIQUE约束是列约束。
您可以使用它为一列实施唯一规则。

要为两个或更多列定义UNIQUE约束,请使用以下语法:

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

使用此语法,您可以在UNIQUE关键字后的括号中添加逗号分隔的列列表。
MySQL使用column_name1和column_name2列中的值组合来评估唯一性。

如果您在未指定名称的情况下定义UNIQUE约束,则MySQL会自动为其生成一个名称。
要使用名称定义UNIQUE约束,请使用以下语法:

[CONSTRAINT constraint_name]
UNIQUE(column_list)

在这种语法中,您可以在CONSTRAINT关键字之后指定UNIQUE约束的名称。

MySQL UNIQUE约束示例

首先,创建一个具有两个唯一约束的名为供应商的新表:

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(15) NOT NULL UNIQUE,
    address VARCHAR(255) NOT NULL,
    PRIMARY KEY (supplier_id),
    CONSTRAINT uc_name_address UNIQUE (name , address)
);

在此示例中,为phone列定义了第一个UNIQUE约束:

phone VARCHAR(12) NOT NULL UNIQUE

第二个约束是名称和地址列:

CONSTRAINT uc_name_address UNIQUE (name , address)

第二,在供应商表中插入一行。

INSERT INTO suppliers(name, phone, address) 
VALUES( 'ABC Inc', 
       '(408)-908-2476',
       '4000 North 1st Street');

第三,尝试插入其他供应商,但其供应商表中已有电话号码。

INSERT INTO suppliers(name, phone, address) 
VALUES( 'XYZ Corporation','(408)-908-2476','3000 North 1st Street');

MySQL发出错误:

Error Code: 1062. Duplicate entry '(408)-908-2476' for key 'phone'

第四,将电话号码更改为另一个,然后再次执行insert语句。

INSERT INTO suppliers(name, phone, address) 
VALUES( 'XYZ Corporation','(408)-908-3333','3000 North 1st Street');

第五,在供应商表中插入一行,其名称已经在列名称和地址中存在:

INSERT INTO suppliers(name, phone, address) 
VALUES( 'ABC Inc', 
       '(408)-908-1111',
       '4000 North 1st Street');

MySQL发出错误,因为违反了UNIQUE约束uc_name_address。

Error Code: 1062. Duplicate entry 'ABC Inc-4000 North 1st Street' for key 'uc_name_address'

MySQL UNIQUE约束和索引

当您定义唯一约束时,MySQL将创建一个相应的UNIQUE索引,并使用该索引来实施规则。

SHOW CREATE TABLE语句显示了Suppliers表的定义:

SHOW CREATE TABLE suppliers;

从输出中可以看到,MySQL在Suppliers表上创建了两个UNIQUE索引:phone和uc_name_address。

以下SHOW INDEX语句显示与Suppliers表关联的所有索引。

SHOW INDEX FROM suppliers;

删除唯一约束

要删除UNIQUE约束,您可以使用DROP INDEX或ALTER TABLE语句:

DROP INDEX index_name ON table_name;
ALTER TABLE table_name
DROP INDEX index_name;

例如,以下语句将uc_name_address约束放置在Suppliers表上:

DROP INDEX uc_name_address ON suppliers;

再次执行SHOW INDEX语句以验证是否已删除uc_name_unique约束。

SHOW INDEX FROM suppliers;

添加新的唯一约束

以下ALTER TABLE ADD CONSTRAINT将唯一约束添加到现有表的列:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE (column_list);

此语句将UNIQUE约束uc_name_address添加回Suppliers表:

ALTER TABLE suppliers
ADD CONSTRAINT uc_name_address 
UNIQUE (name,address);

注意,如果唯一约束中指定的列中的现有数据不符合唯一性规则,则MySQL将不会添加唯一约束。

在本教程中,您学习了如何使用MySQL UNIQUE约束来强制表的一列或一组列中值的唯一性。