MySQL的约束
MySQL中约束保存在information_schema数据库的table_constraints中,可以通过该表查询约束信息;
约束主要完成对数据的检验,保证数据库数据的完整性,如果有相互依赖数据,保证该数据不被删除,因此约束也叫完整性约束。
什么是完整性?完整性是指数据库中存放的数据是有意义的、正确的。
什么是约束?为了保证数据的正确性和相容性,对关系模型提出来的某些约束条件或者规则。
1.默认值(default):当用户向表中插入数据时,指定了该字段的那么就插入该值,否则就插入默认值。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table test(id int(6) primary key auto_increment,name varchar(10) not null default '未知',addr varchar(24) not null default '未知'); Query OK, 0 rows affected (0.07 sec) mysql> desc test; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(6) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | 未知 | | | addr | varchar(24) | NO | | 未知 | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into test values(1,'科比','湖北罗田'); Query OK, 1 row affected (0.01 sec) mysql> insert into test(name) values('毛线'); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +----+--------+--------------+ | id | name | addr | +----+--------+--------------+ | 1 | 科比 | 湖北罗田 | | 2 | 毛线 | 未知 | | 3 | 未知 | 未知 | +----+--------+--------------+ 3 rows in set (0.00 sec)
2.非空(not null):所谓的非空就是字段的名不能输入为null(大小写无所谓),非空不是说不能没有值,也不是说不能为空格。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table weiyi(id int(4) primary key,name char(10) not null); Query OK, 0 rows affected (0.01 sec) mysql> desc weiyi; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | char(10) | NO | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> insert into weiyi(id) values(1); #指定非空可以不输 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into weiyi(id,name) values(2,' '); #可以为空格 Query OK, 1 row affected (0.01 sec) mysql> insert into weiyi(id,name) values(3,Null); ERROR 1048 (23000): Column 'name' cannot be null #不能为空
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table weiyi2(id int(4),name char(10)); Query OK, 0 rows affected (0.03 sec) mysql> insert into weiyi2(id,name) values(1,NULL); Query OK, 1 row affected (0.05 sec)
3.唯一unique:在这个表格中,设置了unique的这个字段,所有的值只能出现一次。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table test_1(id int(2),name char(10) unique); Query OK, 0 rows affected (0.02 sec) mysql> insert into test_1 values(1,'kebi'); Query OK, 1 row affected (0.01 sec) mysql> insert into test_1 values(2,'kebi'); ERROR 1062 (23000): Duplicate entry 'kebi' for key 'name'
4.自增(auto_increment)
建立自增属性有两点要求:
1)字段上要有唯一性索引或者主键
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table zizeng1(id int(4) auto_increment,name char(10)); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
2)该字段必须是数值型
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table zizeng1(id char(4) primary key auto_increment,name char(10)); ERROR 1063 (42000): Incorrect column specifier for column 'id'
自增的表现形式:
在不指定具体的值的时候,设置了自增的字段添加的值会在上一条记录上加一。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select * from auto; +----+------+ | id | name | +----+------+ | 3 | kebi | +----+------+ 1 row in set (0.00 sec) mysql> insert into auto(name) values('maoxian'); Query OK, 1 row affected (0.01 sec) mysql> select * from auto; +----+---------+ | id | name | +----+---------+ | 3 | kebi | | 4 | maoxian | +----+---------+ 2 rows in set (0.00 sec)
如果添加的值比之前的字段值小,会自动往前面添加。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> insert into auto values(1,'xiaoniao'); Query OK, 1 row affected (0.00 sec) mysql> select * from auto; +----+----------+ | id | name | +----+----------+ | 1 | xiaoniao | | 3 | kebi | | 4 | maoxian | +----+----------+ 3 rows in set (0.00 sec)
delete删除值,之后自增的值还是会在之前最大的值的基础上增加。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> insert into auto values(1000,'huangyu'); Query OK, 1 row affected (0.00 sec) mysql> select * from auto; +------+----------+ | id | name | +------+----------+ | 1 | xiaoniao | | 3 | kebi | | 4 | maoxian | | 1000 | huangyu | +------+----------+ 4 rows in set (0.00 sec) mysql> delete from auto; Query OK, 4 rows affected (0.00 sec) mysql> select * from auto; Empty set (0.00 sec) #空的 mysql> insert into auto(name) values('maoxian'); Query OK, 1 row affected (0.01 sec) mysql> select * from auto; +------+---------+ | id | name | +------+---------+ | 1001 | maoxian | #从1001开始 +------+---------+ 1 row in set (0.00 sec)
trancate会清空表,而且也会清空自增的效果。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select * from auto; +------+---------+ | id | name | +------+---------+ | 1001 | maoxian | | 1002 | zidong | | 1003 | zihao | +------+---------+ 3 rows in set (0.00 sec) mysql> truncate table auto; Query OK, 0 rows affected (0.00 sec) mysql> select * from auto; Empty set (0.00 sec) mysql> insert into auto(name) values('zihao'); Query OK, 1 row affected (0.01 sec) mysql> select * from auto; +----+-------+ | id | name | +----+-------+ | 1 | zihao | +----+-------+ 1 row in set (0.00 sec)
5.主键(primary key)
主键是表中的特殊字段,这个字段能够唯一标识表中的每一条记录。
一张表最多只能有一个主键,主键需要满足的条件:非空且唯一。
主键常常与外键构成参照完整性的约束,防止出现数据不一致。
主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引,所以主键也是一个特殊的索引。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table auto(id int(4) primary key auto_increment,name varchar(10));
还有一点需要提出来的是“联合主键”,多个字段的值只要不都不变,就不会报错。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table test(id int(4),name char(10),primary key(id,name)); Query OK, 0 rows affected (0.01 sec) mysql> select * from test; +----+-------+ | id | name | +----+-------+ | 0 | | | 0 | kebi | | 0 | zihao | +----+-------+
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table test2(id int(4) primary key,name char(10)); Query OK, 0 rows affected (0.03 sec) mysql> insert into test2(name) values('maoxian'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into test2(name) values('huangyu'); ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY' #不能满足唯一性
6.外键 (foreign key)
外键是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简而言之,表的外键就是另一张表的主键,外键将两表联系起来。
创建外键的条件:
(1)存储引擎是innodb,存储引擎不是innodb创建是不会报错,但是数据之间失去了关联。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table mem(id int,name varchar(20),foreign key(id) references class(id)) engine=myisam; Query OK, 0 rows affected (0.09 sec) mysql> select * from class; +----+-------+ | id | name | +----+-------+ | 2 | zihao | +----+-------+ 1 row in set (0.00 sec) mysql> insert into mem values(3,'kebi'); Query OK, 1 row affected (0.00 sec)
(2)相关联字段数据类型要一致;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table mem(id int(20),name varchar(20),foreign key(id) references class(id)); Query OK, 0 rows affected (0.10 sec) mysql> select * from class; +----+-------+ | id | name | +----+-------+ | 2 | zihao | +----+-------+ 1 row in set (0.00 sec) mysql> insert into class values(3,'zihao'); Query OK, 1 row affected (0.00 sec)
(3)最好在外键列上建索引(目的就是为了减少扫面范围,不创建也可以,只是影响性能)。
外键的表现形式:
(1)外键列的值依赖主键列的值,如果主键列中没有,那么外键也不能添加
(2)不能删除主键列中被外键列依赖的值。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> create table class(id int primary key,name varchar(20)); Query OK, 0 rows affected (0.08 sec) mysql> create table mem(id int,name varchar(20),foreign key(id) references class(id)); Query OK, 0 rows affected (0.01 sec) mysql> desc class -> ; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc mem; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> insert into class values(1,'kebi'),(2,'zihao'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into mem values(1,'huangyu'); Query OK, 1 row affected (0.00 sec) mysql> insert into mem values(5,'daipeng'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`yueshu`.`mem`, CONSTRAINT `mem_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`id`))
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> select * from class; +----+-------+ | id | name | +----+-------+ | 1 | kebi | | 2 | zihao | +----+-------+ 2 rows in set (0.00 sec) mysql> select * from mem; +------+---------+ | id | name | +------+---------+ | 1 | huangyu | | 2 | daipeng | +------+---------+ 2 rows in set (0.00 sec) mysql> delete from class where id = 1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`yueshu`.`mem`, CONSTRAINT `mem_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`id`)) mysql> delete from mem where id = 1; Query OK, 1 row affected (0.00 sec) mysql> delete from class where id = 1; Query OK, 1 row affected (0.00 sec)
到这里总是觉得外键没有啥用,反而碍手碍脚。怎样让父表不受子表的约束了?
有两种方法:
(1)删除外键约束
(2)指定级联操作的选项。
on delete cascade:级联删除,父表删除记录,子表随之删除相应的记录。
on update cascade级联更新,父表更改数据,子表也会随之更新,但是父表添加,主表没有反应。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#创建外连接,同时指定级联删除和级联更新 mysql> create table mem(id int,name char(10),index(id),foreign key(id) references class(id) on delete cascade on update cascade); Query OK, 0 rows affected (0.01 sec) mysql> select * from class; +----+----------+ | id | name | +----+----------+ | 2 | zihao | | 3 | xiaoniao | +----+----------+ 2 rows in set (0.00 sec) #级联更新 mysql> update class set id = 30 where name = "xiaoniao"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from mem; +------+----------+ | id | name | +------+----------+ | 2 | xiaocui | | 30 | meiliang | +------+----------+ 2 rows in set (0.00 sec) #级联删除 mysql> delete from class where id = 2; Query OK, 1 row affected (0.01 sec) mysql> select * from mem; +------+----------+ | id | name | +------+----------+ | 30 | meiliang | +------+----------+ 1 row in set (0.00 sec) #然而并没有级联更新 mysql> insert into class values(1,'doubi'); Query OK, 1 row affected (0.08 sec) mysql> select * from mem; +------+----------+ | id | name | +------+----------+ | 30 | meiliang | +------+----------+ 1 row in set (0.00 sec)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql> show create table mem; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mem | CREATE TABLE `mem` ( `id` int(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, KEY `id` (`id`), CONSTRAINT `mem_ibfk_1` FOREIGN KEY (`id`) REFERENCES `class` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table mem drop foreign key mem_ibfk_1;外键名,在表的属性中查找 Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table mem; +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ | mem | CREATE TABLE `mem` ( `id` int(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)