mysql> create table vip(
-> id int,
-> name varchar(255) unique,
-> email varchar(255) unique);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into vip(id,name,email) values(2,'lisi','zhangsan@123.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan@123.com' for key 'vip.email'
在以上SQL语句中,我创建vip表时对 name、email 字段分别进行了唯一性约束。
即 name具有唯一性,email具有唯一性。各自唯一,而这种约束其实也叫列级约束!!
如果采用以上方式创建表的话,肯定创建失败,因为 email 字段重复了。
那如何使 name 、email 两个字段联合起来具有唯一性?
( 所谓的联合起来具有唯一性,其实你可以理解为 name或email 字段有一个重复也是可以接受的,但不能两个都重复。)
mysql> create table vip(
-> id int,
-> name varchar(255),
-> email varchar(255),
-> unique(name,email)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into vip(id,name,email) values(2,'lisi','zhangsan@123.com');
Query OK, 1 row affected (0.00 sec)
mysql> select * from vip;
+------+----------+------------------+
| id | name | email |
+------+----------+------------------+
| 1 | zhangsan | zhangsan@123.com |
| 2 | lisi | zhangsan@123.com |
+------+----------+------------------+
2 rows in set (0.00 sec)