MySQL的约束
MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；
约束主要完成对数据的检验，保证数据库数据的完整性，如果有相互依赖数据，保证该数据不被删除，因此约束也叫完整性约束。
什么是完整性？完整性是指数据库中存放的数据是有意义的、正确的。
什么是约束？为了保证数据的正确性和相容性，对关系模型提出来的某些约束条件或者规则。
1.默认值（default）：当用户向表中插入数据时，指定了该字段的那么就插入该值，否则就插入默认值。
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（大小写无所谓），非空不是说不能没有值，也不是说不能为空格。
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)
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 #不能为空
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的这个字段，所有的值只能出现一次。
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）字段上要有唯一性索引或者主键
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）该字段必须是数值型
mysql> create table zizeng1(id char(4) primary key auto_increment,name char(10)); ERROR 1063 (42000): Incorrect column specifier for column 'id'
自增的表现形式：
在不指定具体的值的时候，设置了自增的字段添加的值会在上一条记录上加一。
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)
如果添加的值比之前的字段值小，会自动往前面添加。
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删除值，之后自增的值还是会在之前最大的值的基础上增加。
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会清空表，而且也会清空自增的效果。
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）
主键是表中的特殊字段，这个字段能够唯一标识表中的每一条记录。
一张表最多只能有一个主键，主键需要满足的条件：非空且唯一。
主键常常与外键构成参照完整性的约束，防止出现数据不一致。
主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。
mysql> create table auto(id int(4) primary key auto_increment,name varchar(10));
还有一点需要提出来的是“联合主键”，多个字段的值只要不都不变，就不会报错。
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 | +----+-------+
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创建是不会报错，但是数据之间失去了关联。
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）相关联字段数据类型要一致；
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）不能删除主键列中被外键列依赖的值。
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)
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`))
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级联更新，父表更改数据，子表也会随之更新，但是父表添加，主表没有反应。
#创建外连接，同时指定级联删除和级联更新 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)
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)
