约束条件之外键、外键以及外键字段中表与表之间的关系
约束条件
primary key
主键
- 单从约束角度上而言主键等价于非空且唯一:
not null unique
create table t1(
id int primary key,
name varchar(32)
);
- InnoDB存储引擎规定一张表必须有且只有一个主键
2.1 如果创建的表中没有主键也没有非空且唯一的字段,那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询:新华字典的目录)
2.2 如果创建的表中没有主键但是有非空且唯一的字段,那么InnoDB存储引擎会自动将该字段设置为主键
create table t2(
uid int not null unique,
uuid int not null unique,
name varchar(32)
);
-- 如下图表t2所示,InnoDB存储引擎自动将uid字段设置了为主键
2.3 创建表的时候应该有一个'id'字段,并且该字段应该作为主键
uid sid pid gid cid id
补充说明
1.id int primary key -- 单列主键
2.sid int,
nid int,
primary key(sid,nid) -- 联合主键
auto_increment
自增
- 使用场景:该约束条件不能单独使用,必须跟在键后面(主要配合主键一起使用)
create table t3(
id int auto_increment
);
报错:ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
正确用法:
create table t3(
id int primary key auto_increment,
name varchar(32)
);
-
特点
自增的操作不会因为执行删除数据的操作而回退或者重置
delete from -- 删除表数据之后 再插入数据主键值并没有重置
如果非要重置主键,需要格式化表
truncate 表名; -- 删除表数据并重置主键值
约束条件之外键
不同外键情况下建立员工表和部门表之间的关系
上述表的缺陷
-
表结构不清晰,到底是员工表还是部门表
-
字段数据反复存取,浪费存储空间
-
表的扩展性极差,牵一发而动全身(重要)
我们可以进行优化操作,拆表,然后添加外键
-- user
create table user(
id int primary key auto_increment,
name varchar(32),
dept_id int,
foreign key(dept_id) references dept(id) -- 创建外键,和部门表关联
on update cascade
on delete cascade
)
-- dept
create table dept(
id int primary key auto_increment,
name varchar(32),
dept_desc varchar(60)
);
外键字段的创建
外键字段是用来记录表与表之间的数据关系,而数据关系分为如下四种
-
一对多关系
-
多对多关系
-
一对一关系
-
没有关系
表数据关系的判定(换位思考)
针对员工表和部门表判断数据关系
1.先站在员工表的角度
问:一条员工数据能否对应多条部门数据
翻:一名员工能否属于多个部门
答:不可以
2.再站在部门表的角度
问:一条部门数据能否对应多条员工数据
翻:一个部门能否拥有多个员工
答:可以
完成换位思考之后得出的答案:一个可以一个不可以
那么表关系就是:一对多
针对'一对多'的关系,外键字段建在多的一方
ps:没有多对一,统称为'一对多'
foreign key
-
创建表的时候需要先创建被关联的表(没有外键),然后再是关联表(有外键)
-
插入表数据的时候,针对外键字段只能填写被关联字段已经出现过的数据值
-
被关联字段无法修改和删除,有点不太好,操作限制性太强
-
通过级联更新、级联删除(被关联数据一旦变动,关联的数据同步变动)
-- user
create table user(
id int primary key auto_increment,
name varchar(32),
dept_id int,
foreign key(dept_id) references dept(id) -- 创建外键,和部门表关联
on update cascade -- 级联更新
on delete cascade -- 级联删除
)
-- dept
create table dept(
id int primary key auto_increment,
name varchar(32),
dept_desc varchar(60)
);
在实际工作中,很多时候可能并不会使用外键,因为外键增加了表之间的耦合度,不便于单独操作,资源消耗增加
我们为了能够描述出表数据的关系,又不想使用外键,可以通过自己写sql,建立 代码层面的关系
表关系之多对多
以书籍表和作者表为例
-
先站在书籍表的角度
问:一本书能否对应多个作者 答:可以
-
再站在作者表的角度
问:一个作者能否对应多本书 答:可以
-
总结
两边都可以,那么表数据关系就是:多对多
针对多对对表关系,外键字段不能建立在任何一方!
-- book
create table book(
id int primary key auto_increment,
title varchar(32)
);
-- author
create table author(
id int primary key auto_increment,
name varchar(32)
);
-- book_author中间表
create table book_author(
id int primary key auto_increment,
book_id int,
author_id int,
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
);
表关系之一对一
以用户表和用户详情表
-
先站在用户表的角度
问:一个用户是否能对应不同的用户详情 答:不可以
-
再站在用户详情表的角度
问:一个用户详情是否能对应不同的用户 答:不可以
-
总结
两边都不可以,那么先考虑是不是没有关系
如果有关系,那么肯定就是>>>:一对一
针对一对一的表关系,外键字段建立在任何一张表都可以,但是建立建立在查询频率较高的那张表中
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references user_detail(id)
on update cascade
on delete cascade
);
create table user_detail(
id int primary key auto_increment,
phone bigint,
addr varchar(32)
);
作业
"""判断下列表数据关系 并自定义创建出表
ps:有些表数据关系不是确定 根据具体业务可能有变化
服务器表与应用程序表
课程表与班级表
学生表与班级表
老师表与课程表
书籍表与出版社表"""
-- 1.服务器表与应用程序表
服务器可以对应多个应用程序
应用程序也可以对应多个服务器:>>>多对多
create table server(
id int primary key auto_increment comment '编号',
model varchar(32) not null comment '型号'
);
create table program(
id int primary key auto_increment comment '编号',
name varchar(32) unique comment '程序名'
);
create table server_program(
id int primary key auto_increment comment '编号',
server_id int comment '服务器编号',
program_id int comment '程序编号',
foreign key(server_id) references server(id)
on update cascade
on delete cascade,
foreign key(program_id) references program(id)
on update cascade
on delete cascade
);
-- 2.课程表与班级表
一个课程可以对应不同的班级
一个班级可以对应不同的课程>>>:多对多
create table course(
id int primary key auto_increment,
name varchar(32)
);
create table class(
id int primary key auto_increment,
name varchar(32)
);
create table course_class(
id int primary key auto_increment,
course_id int,
class_id int,
foreign key(course_id) references course(id)
on update cascade
on delete cascade,
foreign key(class_id) references class(id)
on update cascade
on delete cascade
);
-- 3.学生表与班级表
一对多
create table student(
id int primary key auto_increment,
name varchar(32),
age int,
class_id int,
foreign key(class_id) references class(id)
on update cascade
on delete cascade
)
create table class(
id int primary key auto_increment,
name varchar(20)
)
-- 4.老师表与课程表
一个老师只能对应一门课程
一门课程只能对应一个老师>>>一对一
create table teacher(
id int primary key auto_increment,
name varchar(20),
course_id int unique, -- 一对一与多对一建表语句的唯一区别:unique
foreign key(course_id) references course(id)
on update cascade
on delete cascade
);
create table course(
id int primary key auto_increment,
name varchar(32)
);
-- 5.书籍表与出版社表
一本书只能对应一个出版社
一个出版社有对应多本书:>>>:一对多
create table book(
id int primary key auto_increment,
name varchar(32),
publish_id int,
foreign key(publish_id) references publish(id)
on update cascade
on delete cascade
)
create table publish(
id int primary key auto_increment,
name varchar(32)
)