约束条件之外键、外键以及外键字段中表与表之间的关系

约束条件

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)
)

posted @ 2022-08-16 16:34 荀飞阅读(53) 评论(0) 编辑收藏举报

刷新页面返回顶部