六:约束
1.约束
字段类型之外对字段加的限制，来保证数据的完整性和一致性。
例如设计的表
为了防止不符合规范的数据录入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效。
约束条件与数据类型的宽度一样，都是可选参数
主要分:
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录，自动添加索引 FOREIGN KEY (FK) 标识该字段为该表的外键，外键不会添加索引。 NOT NULL 标识该字段不能为空 UNIQUE KEY (UK) 标识该字段的值是唯一的，自动添加索引。 AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） DEFAULT 为该字段设置默认值 UNSIGNED 无符号 ZEROFILL 使用0填充
1. 是否允许为空，默认NULL.可设置NOT NULL，字段不允许为空，必须赋值这时就可以连用default了。
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)
2.nou null和default
当字段没有约束，而且没有传值，那么默认传Null，因为自动在后面加了default null。
当约束为not null时，不允许为null，这里最好加上default 默认值约束，因为你不允许为空，一旦你没有给此字段传值，mysql就不知道你要是传值时应该放什么值进去。
为了健壮性，最好为not null约束的字段加上默认值约束。
null视为一个关键字，他不是一个数值，无法进行比较，即便是unique唯一性约束，也无法对null进行比较。
空字符串，mysql认为是重复的。null不算重复，空字符串算重复。
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)
3.unique
唯一约束，指定某列或者几列组合非空不能重复，是唯一的。例如name id 手机号
mysql认为null不是重复的,所以当你需要制定某字段非空且唯一时,not null unique
例如用户名不能重复
联合唯一
create table service( id int primary key auto_increment, name varchar(20), host varchar(15) not null, port int not null, unique(host,port) #联合唯一 ); mysql> insert into service values -> (1,'nginx','192.168.0.10',80), -> (2,'haproxy','192.168.0.20',80), -> (3,'mysql','192.168.0.30',3306) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
create table user_tb( id int, username varchar(32) not null, num int not null, unique(username,num) );
这里unique key 'username' ("username","num")
括号外面的username,表示联合约束加在了username上
姓名可以重复,但是姓名和学号不能重复.
4.primary key
等同于unique+not null，但是primary key会自动添加索引，一张表有且只能有一个主键
desc表可以看到都有一列是key这是索引，可以加速查询
一张表只能有一个主键,id int primary_key auto_increment,这是99%的使用方式
一张表可以没有主键,记得一次面试问过这个问题,我答的是必须有主键,面试官一脸傲娇的说不一定非有主键
但是你表不设置主键?外键也问一堆,一看就是公司不行,直接走了,整了一个数据库单玩呗,过家家啊.
外键在生产中你设置了就是给自己找麻烦,千万不要让数据库来繁重的工作,可以在上层中解决的问题,不要把请求打到数据库,可以在redis缓存中解决的问题也不要把请求打到数据库.
一旦表设置了外键,向分布式迁移的时候,涉及分库分表,难搞的要死.
虽然是学数据库,但是尽可能在逻辑层把问题解决,一旦需求变了,只改逻辑层,不动数据库的表结构.
尤其是数据量非常大的时候.
因为我们人是活的,思维是活的,数据库就是一个死干活的,它只知道你让它这么干,但是它干的慢,后面那么多请求阻塞着呢!
联合主键
很少见，但是也只能有一个主键。
create table user_tb( id int, username varchar(32) not null, num int not null, primary key(username,num) );
5. auto_increment
只能用在数字类型上且必须是唯一的,因此常和主键一起使用,由Mysql自动帮你维护id字段，只能给有key约束的字段加。
key有unique primary key还有一个index key，都能加速查询，前2个还有约束的效果。
你只关心核心的数据
6.foreign key
why? what?
外键是为了让表的列的数据的可填入值，约束在对应的范围内！
那么为什么不用set呢？
一旦可填入数值的范围变化了，就要对表结构进行修改，一个表很大时频繁的修改表结构，会耗费大量的时间。
how?
将A表的一列可填入的数值，约束在B表的其中一列，此时在想被约束的A表一列加上外键，关联到B表的对应列。
这样A表和B表就建立了关系，建表只能先建立普通表，然后建立含有外键的表，修改的时候，如果外键字段要修改，一定要在关联的字段指定范围内，如果不在会报错。
如果单独修改班级名称，直接修改父表B即可，A表中的外键字段不需要改动，除非说A表中某一个人更换班级。
即：小表驱动大表，外键字段加在多方。后面后又各种表关系，以及变种。
优化？外键多了不是会影响效率么
外键多了确实影响效率，github中的项目，有的表与表的关系不靠外键，也是将两个表的关系进行了类似外键的设计，但是却没有加外键
这样做的前提是对项目足够的了解，一旦来个新人，插入了一条数据没有约定在指定范围内，那么这条数据就是脏数据。
那样做的前提是，例如A表的班级约定在1班-10班，新人不知道，插入了一条11班，它不受约束，一旦以班级为关键字查询，业务中是没有11班的，那么这条数据永远沉睡在表中。
即：约定确实是约定，但是不加以限制，就只是口头约定，总会有人踏出那条红线。
表与表建立关系，所有数据不可能都放一张表中，一有冗余，二占用硬盘空间大，三一旦修改和扩展十分麻烦
涉及表的时候通过解耦把一张表分不到多个表中，用外键建立关系
员工部门表，多个员工属于一个部门，是多对一，一个部门有多个员工，是一对多，站在不同表的角度。
步骤：先站在一张表，看和另一张表的关系，左表有没有多对一右表的关系，左边表的多条记录可否对应右表一条记录
再站到右边表的角度，看能否多条记录对应左表的一条记录，如果都可以就是多对多，如果只有一个满足就是一对多，都不满足就是一对一
在多方添加外键字段指向一表的id字段，这样两个表的硬性关系就建立了，多表的外键字段必须来自一表的id字段，这样硬性关系就建立了。
这里有个重点，只是建立了逻辑上的关系，还没有建立外键上的硬性关系
foreign key(dep_id) references dep(id)加上了这个之后就会建立了表上面的硬性关系，而不是逻辑上的关系，会增加表的复杂度，迁移也会变得困难。
有了外键之后的约束：
1.必须先建立被关联的表，然后建立关联的表
2.插入记录时，必须先往被关联表插入数据，然后往关联的表插入记录。
3.更新与删除时，都需要考虑到关联和被关联的关系，先删除关联，再删除被关联表，同步更新同步删除
foreign key(dep_id) references dep(id) on update cascade on delete cascade
多对多关系
书表 作者表 多本书可以对一个作者，多个作者可以对一本书
因此是多对多的关系，此时需要一个中间表，保存两个多表之间的关系，将外键字段加在新的表中，分别取关联两个多表的id
先把两个多表建立好
然后创建新的关联表，加两个外键
foreign key(book_id) references book(id) on update cascade on delete cascade
foreign key(author_id) references author(id) on update cascade on delete cascade
一对一关系
潜在的客户表，与已经成为客户的表是一对一的关系，先看有哪个记录，先有的潜在客户，然后才会发展为真正的客户，那么就要将外键加在后创建的表中。
先成为潜在客户，才会有可能成为客户，所以加在客户表中，只是和其他不同的是外键字段要多一个unique约束，来保证不会重复，就是一对一了。
几乎不用,稍微看看,实际开发中不加外键，在逻辑上面控制。
涉及多张表,加在数据多的表中,想要删除有外键表的记录
外键一般是关联外表的主键,因为上面有索引
mysql> create table departments (dep_id int(4),dep_name varchar(11)); Query OK, 0 rows affected (0.02 sec) mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | YES | | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) # 创建外键不成功 mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); ERROR 1215 (HY000): Cannot add foreign key # 设置dep_id非空，仍然不能成功创建外键 mysql> alter table departments modify dep_id int(4) not null; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | NO | | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); ERROR 1215 (HY000): Cannot add foreign key constraint # 当设置字段为unique唯一字段时，设置该字段为外键成功 mysql> alter table departments modify dep_id int(4) unique; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc departments;
+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | YES | UNI | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> create table staff_info (
s_id int,
name varchar(20),
dep_id int,
foreign key(dep_id) references departments(dep_id)
); Query OK, 0 rows affected (0.02 sec)
