MySQL之约束条件

约束条件

primary key

primary key主键 单从约束角度讲主键等价于非空且唯一not null unique
1.创建一个非空且唯一的表
create table p1(id int not null unique,name varchar(32));
insert into p1 values(1,'summer');
insert into p1 values(1,'jason');  
# 结果报错 见下图 ERROR 1062 (23000): Duplicate entry '1' for key 'id'
2.创建一个primary key主键表
 create table p2(id int primary key,name varchar(32));
 insert into p2 values(1,'summer');
 insert into p2 values(1,'summer');  
 # 结果报错 见下图 ERROR 1062 (23000): Duplicate entry '1' for key'PRIMARY' 

# innoDB存储引擎规定，一张表必须有且只能有一个主键

1.如果创建的表没有主键且没有非空且唯一的字段，那么innoDB会采取隐藏的字段作为主键
ps：主键是一种唯一索引，目的是为了增加数据的查找速度，唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的
------------

2.如果创建的表中没有主键，但是有非空且唯一的字段，那么该字段会被innoDB设置作为主键
create table p1(id int not null unique,name varchar(32));

------------

3.当表中存在多个非空的字段的时候，InnoDB存储引擎会根据建表时所创建的第一个非空唯一字段作为主键。
create table a1(id int not null unique,uid int not null unique,sid int not null unique);

------------
4.创建表的时候都应该有id字段，那么该字段应该作为主键

ps: id int primary key 是单列主键 
    sid int   uid int primary key（sid，uid）是联合主键

auto_increment

auto_increment自增 该条件不可以单独使用 必须要有主键配合一起使用
create table a3(id int auto_increment);
# 报错 Incorrect table definition; there can be only one auto column and it must be defined as a key
create table a3(id int primary key auto_increment);  # 这样才可以篡创建成功

autoi_ncrement自增的特点是：
1.自增的操作不会因为删除数据的操作回退或者重置
2.如果非要重置数据的话 只能格式化 命令truncate 表名
3.如果删除了数据4 ，那么下一次创建数据的自增从5开始
create table a3(id int primary key auto_increment);
insert into a3 values(1);
insert into a3 values();
insert into a3 values();
insert into a3 values();
delete from a3 where id=4;
 insert into a3 values();  # 5

约束条件之外键

如何理解外键

1.先创建一张表（图1） 里面的内容有：
id name gender	dep_name dep_desc
# 从表的内容看 很难分辨这到底是一张员工表还是一张部门表 表结构不清晰
# 表的部门数据反复存取 浪费存储空间
# 如果要修改部门名字的话 那么需要修改所有在部门的人员，扩展性差！
2.把上述表拆分成 员工表和部门表 结构更清晰 但是有一个问题 两张表的数据是没有办法看出来哪个员工在哪个部门的
3.给部门表创建id 用序号来表示部门 这样的话以后给员工更换部门直接修改序号即可 图2
4.外键就是dep_id 用于记录表与表之间的数据关系

图1：

图2：

外键的介绍

由上述推导可以得出结论：外键字段是用来记录表与表之间的数据关系
而数据关系分为三种：
		1.一对多关系
		2.多对多关系
		3.一对一关系
				

表数据关系的判定‘换位思考’

1.一张员工表和一张公司部门表
	如何判断员工表与部门表的数据关系
	
	# 站在员工角度
	一个员工是否可以同时在多个部门工作？？
	答案是：（实际情况是）不可以
	
	# 站在部门角度
	一个部门是否可以有多个员工？？
	答案是：可以
ps: 换位思考得出的结论是，一个可以（部门），一个不可以（员工）
那么表关系就是一对多 ，部门是一，员工是多
# 针对一对多关系 外键字段在多的那一方  （只有一对多，没有多对一！！！！）

foreign key

写入外键

1.创建表
先创建被关联的表（部门表），再创建关联的表（员工表）
create table dep(id int primary key auto_increment,name varchar(32),dep_desc varchar(32));
 create table user(id int primary key auto_increment,name varchar(32),gender enum('male','female') default 'male',dep_id int,foreign key (dep_id) references dep(id));
2.写入数据
先写部门表的数据 ，再写员工表的数据 
# 针对外键字段 只能填写被关联表里有的数据值！！！！
被关联字段无法修改和删除
insert into dep values(1,'技术部门','python工程师'),(2,'后勤部门','伙夫'),(3,'开发部门','java工程师');
insert into user values(1,'summer','female',1);
insert into user(name,gender,dep_id) values('meimei','male',2);
insert into user(name,gender,dep_id) values('jason','male',2);
insert into user(name,gender,dep_id) values('lili','male',3);

级联更新和级联删除

# 被关联的数据一旦修改或者删除 ，关联的数据也随之变动
on update cascade on delete cascade

create table aaaa(id int primary key auto_increment,name varchar(32),dep_desc varchar(32));
create table dddd(id int primary key auto_increment,name varchar(32),gender enum('male','female') default 'male',dep_id int,foreign key (dep_id) references aaaa(id) on update cascade on delete cascade);

insert into aaaa values(1,'技术部门','python工程师'),(2,'后勤部门','伙夫'),(3,'开发部门','java工程师');
 insert into dddd values(1,'summer','female',1);
 insert into dddd(name,gender,dep_id) values('meimei','male',2);
 insert into dddd(name,gender,dep_id) values('jason','male',2);
 insert into dddd(name,gender,dep_id) values('jerry','male',3);
# 修改id号
 update aaaa set id = 666 where id=2;


# ps：外键增加了表的耦合度，不便于独立操作，资源消耗增加，一般情况不建议使用外键，如有需要可以自己写MySQL建立代码层面的关系

修改前：

修改后：

表关系之多对多

多对多概念的介绍

以书籍和作者的角度来看
1.站在作者的角度 一个作者是否可以写多本书？
答案是：可以
2.站在书籍的角度 一本书是否可以被多个作者编写？
答案是：可以
两边都可以那么表数据关系就是多对多！
针对多对多关系 外键字段不可以建在任意一方

多对多写入

create table author(id int primary key auto_increment,name varchar(32));
 create table book(id int primary key auto_increment,book_name varchar(32));
 create table book_author(id int primary key auto_increment,book_id int,foreign key(book_id) references book(id) on update cascade on delete cascade,author_id int,foreign key(author_id) references author(id) on update cascade on delete cascade);

insert into book_author values(1,'1','1'),(2,'1','2'),(3,'2','3');

表关系之一对一

一对一概念

eg:身份证
1.站在用户角度 一个用户可以拥有多个身份证吗？
答案：不可以
2.站在身份证的角度 一个身份证可以有多个用户吗？
答案：不可以
如果答案是都不可以 要么是表与表数据没有关系 有关系就是一对一关系
针对一对一关系数据表 外键建在哪一方都可以 建议建在使用频率较高的一方

一对一写入

create table muser(id int primary key auto_increment,num bigint);
create table myID(id int primary key,name varchar(32),muser_id int unique,foreign key(muser_id) references muser(id) on update cascade on delete cascade);