MySQL中对三种约束的支持:[url]http://leekai.me/mysql-zhong-dui-san-zhong-yue-shu-de-zhi-chi/[/url]
MySQL的InnoDB默认支持5中约束,
– primary key
– unique key
– foreign key
– default
– not null
今天我们来操作一下唯一约束创建与删除,首先创建表:
查看表描述:
添加唯一约束:
查看表创建语句:唯一约束已添加
删除唯一约束:注意删除约束的方式,是drop INDEX,而不是drop INDEX;
添加联合唯一约束:
MySQL的InnoDB默认支持5中约束,
– primary key
– unique key
– foreign key
– default
– not null
今天我们来操作一下唯一约束创建与删除,首先创建表:
CREATE TABLE `user1` (
`name` varchar(20) NOT NULL,
`email` varchar(100) default NULL,
`phone` varchar(11) default NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
查看表描述:
mysql> use test;
Database changed
mysql> desc user1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(20) | NO | PRI | | |
| email | varchar(100) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set
添加唯一约束:
mysql> alter table user1 add constraint emailUnique UNIQUE(email);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(20) | NO | PRI | | |
| email | varchar(100) | YES | UNI | NULL | |
| phone | varchar(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set
查看表创建语句:唯一约束已添加
mysql> show create table user1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
`name` varchar(20) NOT NULL,
`email` varchar(100) default NULL,
`phone` varchar(11) default NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `emailUnique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> show index from user1;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user1 | 0 | PRIMARY | 1 | name | A | 0 | NULL | NULL | | BTREE | |
| user1 | 0 | emailUnique | 1 | email | A | 0 | NULL | NULL | YES | BTREE | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set
删除唯一约束:注意删除约束的方式,是drop INDEX,而不是drop INDEX;
mysql> alter table user1 drop INDEX emailUnique;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from user1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user1 | 0 | PRIMARY | 1 | name | A | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set
mysql> show create table user1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
`name` varchar(20) NOT NULL,
`email` varchar(100) default NULL,
`phone` varchar(11) default NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> desc user1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(20) | NO | PRI | | |
| email | varchar(100) | YES | | NULL | |
| phone | varchar(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set
mysql>
添加联合唯一约束:
mysql> alter table user1 add constraint email_Phone_Unique UNIQUE(email,phone);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(20) | NO | PRI | | |
| email | varchar(100) | YES | MUL | NULL | |
| phone | varchar(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set
mysql> show create table user1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
`name` varchar(20) NOT NULL,
`email` varchar(100) default NULL,
`phone` varchar(11) default NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `email_Phone_Unique` (`email`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> show index from user1;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user1 | 0 | PRIMARY | 1 | name | A | 0 | NULL | NULL | | BTREE | |
| user1 | 0 | email_Phone_Unique | 1 | email | A | 0 | NULL | NULL | YES | BTREE | |
| user1 | 0 | email_Phone_Unique | 2 | phone | A | 0 | NULL | NULL | YES | BTREE | |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set
mysql>