MySQL约束条件之主键与外键

主键与外键

 

primary key主键

'''
但从约束层面上来说 相当于是 not null + unique(非空且唯一)
在此基础之上还可以加快数据的查询
    
InnoDB存储引擎规定了一张表必须有且只有一个主键
　　因为InnoDB是通过主键的方式来构造表的
　　如果没有设置主键
　　　　情况1:没有主键和其他约束条件
　　　　　　InnoDB会采用隐藏的字段作为主键 不能加快数据的查询
　　　　情况2:没有主键但是有非空且唯一的字段
　　　　　　自动将该字段升级为主键
　　　　　　create table t6(
　　　　　　　　id int,
　　　　　　　　age int not null unique,
　　　　　　　　pwd int not null unique
　　　　　　);
结论:
　　以后我们在创建表的时候一定要设置主键
　　并且主键字段一般都是表的id字段(uid sid pid cid)
'''
create table user(
　　id int primary key,
　　name varchar(32)
);

auto_increment自增

'''
由于主键类似于数据的唯一标识 并且主键一般都是数字类型
我们在添加数据的时候不可能记住接下来的序号是多少 太麻烦
'''
create table user1(
    id int primary key auto_increment,
    name varchar(32)
);

'''
自增的特性
    自增不会因为删除操作而回退
    delete from无法影响自增
    如果想要重置需需要使用truncate关键字
    truncate 表名  # 清空表数据并且重置主键值
'''

foreign key外键

　　1.现有表分析　　　

　　　　创建一张员工表
　　　　id name age dep_name dep_desc

　　　　缺陷　　

　　　　　　1.表的重点不清晰 　　　　　　　　      可以忽略
　　　　　　　　到底是员工表还是部门表
　　　　　　2.表中相关字段一直在重复存储 　　      可以忽略
　　　　　　　　浪费存储空间
　　　　　　3.表的扩展性极差,牵一发而动全身 　　 不能忽略

　　　　解决方式

　　　　　　将上述一张表拆分成两张表
　　　　　　emp与dep

　　　　结论

　　　　　　上述三个缺陷全部解决但带来了一个小问题 表与表之间的数据没有对应关系了

　　外键字段>>>:部门编号
    　　其实就是用来标识表与表之间的数据关系
   # 简单的理解为该字段可以让你去到其他表中查找数据

　　2.表与表之间建关系

　　　　判断表关系的方式:换位思考

　　　　1.一对多

　　　　以员工和部门表为例

　　　　　　先站在员工表的基础之上
　　　　　　　　问:一个员工信息能否对应多个部门信息
　　　　　　　　答:不可以

　　　　　　再站在部门表的基础之上
　　　　　　　　问:一个部门信息能否对应多个员工信息
　　　　　　　　答:可以

　　　　　　结论:一个可以一个不可以 那么表关系就是"一对多"
　　　　　　　　员工表是多 部门表是一

　　　　针对一对多的表关系 外键字段建在多的一方

# 表关系没有'多对一'一说 都是'一对多'

'''
使用SQL语句建立真正意义上的表关系 可以先创建不含外键字段的基本表
之后再添加外键字段
'''
    
create table dep(
　　id int primary key auto_increment,
　　dep_name varchar(32),
　　dep_desc varchar(254)
);
create table emp(
　　id int primary key auto_increment,
　　name varchar(32),
　　age int,
　　dep_id int,
　　foreign key(dep_id) references dep(id)
);

　　　　2.多对多关系

　　　　以书籍表与作者表为例

　　　　　　先站在书籍表的基础之上
　　　　　　　　问:一个书籍信息能否对应多个作者信息
　　　　　　　　答:可以

　　　　　　再站在作者表的基础之上
　　　　　　　　问:一个作者信息能否对应多个书籍信息
　　　　　　　　答:可以

　　　　　　结论:两个都可以 那么表关系就是"多对多"

# 多对多表关系 需要单独开设第三张表存储(并且第三张表可以不绑定)

create table book(
　　id int primary key auto_increment,
　　title varchar(32),
　　price float(6,2)
);
create table author(
　　id int primary key auto_increment,
　　name varchar(32),
　　age int
);
create table book2author(
　　id int primary key auto_increment,
　　author_id int,
　　book_id int,
　　foreign key(author_id) references author(id)
　　on update cascade   # 级联更新
　　on delete cascade,  # 级联删除
　　foreign key(book_id) references book(id)
　　on update cascade   # 级联更新
　　on delete cascade   # 级联删除
);

　　　　3.一对一表关系

　　　　作者表与作者详情表

　　　　　　先站在作者表的基础之上
　　　　　　　　问:一个作者信息能否对应多个作者详情信息
　　　　　　　　答:不可以

　　　　　　再站在作者详情表的基础之上
　　　　　　　　问:一个作者详情信息能否对应多个作者信息
　　　　　　　　答:不可以

　　　　　　结论:两个都不可以
　　　　　　　　那么表关系可能是"一对一"或者"没有关系"

# 外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中

create table author_detail(
　　id int primary key auto_increment,
　　phone varchar(32),
　　address varchar(32)
);
create table author(
　　id int primary key auto_increment,
　　name varchar(32),
　　age int,
　　author_id int unique,
　　foreign key(author_id) references author_detail(id)
　　on update cascade  # 级联更新
　　on delete cascade  # 级联删除
);

　　3.外键约束

　　　　1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
　　　　2.在插入新数据的时候 应该先确保被关联表中有数据
　　　　3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
　　　　4.在修改和删除被关联表中的数据的时候 无法直接操作

# 如果想要数据之间自动修改和删除需要添加额外的配置

create table dep1(
　　id int primary key auto_increment,
　　dep_name varchar(32),
　　dep_desc varchar(254)
);
create table emp1(
　　id int primary key auto_increment,
　　name varchar(32),
　　age int,
　　dep_id int,
　　foreign key(dep_id) references dep1(id) 
　　on update cascade  # 级联更新
　　on delete cascade  # 级联删除
);

'''
由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度
　　所以在实际开发项目中 有时候并不会使用外键创建表关系
　　而是通过SQL语句层面 建立逻辑意义上的表关系
　　　　eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql
'''

 

END