主键与外键

主键与外键

约束条件之主键

• primary key主键

  1.在InnoDB存储引擎中规定了只要是我空间中的一张表那么就必须要有一个主键，单从约束角度上而言的话主键其实等于非空且唯一，如果没有主键的话那么InnoDB就会帮你自动采用一个隐藏的字段作为他的主键（主键的作用其实就是可以帮我们快速的查找数据，就像新华字典的目录，我们可以根据目录的方法，查看我们所需要的目标在哪里，并找到他），如果创建的表中有非空且唯一的字段那么引擎就会将他自动设置为主键
  	1.1非空且唯一  not null(非空) unique(唯一性)
  		create table t1(
          id int primary key,
          name varchar(32)
          );
  		desc ti;  # id key PRI
  	1.2测试主键
      create table t2(
          nid int not null unique,
          sid int not null unique,
  		  uid int not null unique,
  		  name varchar(32)
      );
  	desc t2;  # nid key PRI  sid key UNI  uid key UNI
  	1.3补充，在创建表的时候都应该有一个'id'字段，并且该字段应该作为主键例如：id, nid, sid, uid, pid, gid, cid等；
      id int primary key 单例主键
      nid int,
      sid int,
      primary key(nid,sid)  联合主键
  

• auto_increment自增

  1.在我们使用自增的时候一定要注意，它必须配合在别的键后方然后才可以使用自增，是不可以单独使用的。并且在自增的的操作并不会因为你删除了下一个就可以接替上一个编码继续使用，他会默认存储那个已经使用过了，如果非要重置表格的话就需要进行格式化处理。
  	1.1自增(错误演示)
      create table t3(
          id int auto_increment
      );  # 直接使用的话那么就会报错，there can be only one auto column and it must be defined sa a key
  	1.2自增（正确演示）
  	create table t4(
          id int primary key auto_increment,
          name varchar(32)
      );
    1.3删除与重装格式化
  	delete from(删除)
  	truncate 表名；  # 删除内部数据并且充值主键值
  

约束条件之外键

• 前戏（需求我们需要一张员工表）

  id	name	gender	dep_name	dep_desc
  1	Joseph	male	校长	新时代，新气象，新方法，新hhxx渠道
  2	Cindy	female	学生	好好学习天天向上
  3	Alice	female	老师	教书育人
  4	Kety	female	教务处主任	维持秩序
  5	Zoe	female	学生	好好学习天天向上
  6	Emily	female	学生	好好学习

  • 我们创建了一张表然后发现这张表其实有很多的缺陷

    • 表结构并不清晰，分不清这个表到底主要是存储什么内容
    • 字段数据反复存取，较为浪费存储空间
    • 表的扩展性极差，牵一发而动全身，所有相关的内容都需要更改并且更改还比较麻烦

  • 优化操作>>>:拆表（将表细分成多个表）

  id	name	gender	dep_id
  1	Joseph	male	1
  2	Cindy	female	4
  3	Alice	femlae	3
  4	Kety	female	2
  5	Zoe	female	4
  6	Emily	female	4

  id	dep_name	dep_desc
  1	校长	新时代，新气象，新方法，新hhxx渠道
  2	教务处主任	维持秩序
  3	老师	教书育人
  4	学生	好好学习天天向上

  • 拆表后我们解决了上方但是出现一个致命问题就是两个表之间没有关系了
  • 那么我们就需要给她加上一个部门数据的主键值，专门用于记录表与表之间的关系

• 外键字段的创建

  • 外键字段其实就是用来记录表语表之间关系的数据，而数据的关系有四种
    • 一对多关系
    • 多对多关系
    • 一对一关系
    • 没有任何关系
  • 表数据关系的判定 >>>: '换位思考'
    • 针对人员表和职能表判断数据之间的关系
      • 问：一个人员能否对应多种职能
      • 答：不可以
    • 站在职能表的角度来看与人员表的关系
      • 问：我这个职能可不可以拥有很多人
      • 答：可以
  • 我们在经过探究观察发现，我们的人员表是不可以符合一个人有很多职能的，但是职能表中同一个量级内可以拥有多个人员,所以我们这两个表之间的关系就是"一对多"，一对多的情况下我们的关键字段一般建立在较多的那一方，以方便调用

约束条件之一对多

• 一对多外键字段

  1.在我们将表与表之间进行关联的时候就需要使用外键来当作他的两个表之间的记录关系。
  2.一
  	create table dep(
  		id int primary key auto_increment,
  		dep_name varchar(32),
  		dep_desc varcahar(32)
      );
  3.多
  	create table emp(
  		id int primary key auto_increment,
  		name varchar(32),
  		gender enum('amle','female','others')default'male',
  		dep_id int,
  		froeign key(dep_id)references dep(id)
      );
  4.补充
  	4.1创建表的时候需要先创建被关联的表(没有外键的表)然后才能创建有关联的表(有外键),如果我们先创建有关联的表那么就会因为苦衷没有该被关联的表然后报错,不符合逻辑,所以我们需要先创建无关联的表然后再进行关联表的创建
  	4.2在我们插入数据的时候,针对外键字段只能够填充被关联字段中出现过的数据值,如果没有出现的话,那么就会因为没有这个检索而报错
  	4.3被关联的字段无法进行修改和删除
  

• 级联更新，级联删除

  1.我们既然需要关联那么我们就需要这边改完数据然后那边也改,但是这样比较麻烦并且我们的还是关联的文件没办法直接修改所以我们就需要级联更新,修改一个那么和他有关联的全部都修改
  	1.1级联更新,级联删除
  	create table dep(
  		id int primary key auto_increment,
  		dep_name varchar(32),
  		dep_desc varchar(32)
      );
  	create table emp1(
  		id int primary key auto_increment,
  		name varchar(32),
  		gender enum('male','female','others')default 'male',
  		dep_id int,
  		froeign key(dep_id) references depl(id)
  		on update cascade
  		on delete cascade
      );
  2.补充
  	2.1我们在实际工作中,很多地方可能并不会使用外键,因为外键增加了表之间的耦合度,不便于操作,资源消耗增加,并且如果全部使用外键那么会导致修改一个数据然后整个数据系统就像翻新了一样,导致不便
  	2.2我们为了能够描述出表数据之间的关系,又不想使用外键的话就应该自己写SQL,自己建立代码层面的关系
  

约束条件之多对多

• 前戏(创建一个书籍表和一个作者表)

  • 站在书籍的角度来看

    • 一本书的作者信息能不能对应多条作者数据,也就是说一本书能不能有多个作者
    • 可以

  • 站在作者的标度来看

    • 一个作者能不能对应很多本书的信息,也就是说一个作者能否写很多本书
    • 可以

    create table book(
    	id int primary key auto_increment,
    	title varchar(32),
    	author_id int,
    	foreign key(author_id) references author(id)
    	on update cascade
    	on delete cascade
    );
    create table author(
    	id int primary key auto_increment,
    	name varchar(32),
    	book_id int,
    	foreign key(book_id) references book(id)
    	on update cascade
    	on delete cascade
    );
    """
    双方互相调用好像不太行的样子
    """
    

  • 那么我们的这两张表其实有很多信息都是可以对应起来的并且有可能重叠对应,那么我们就可以称之为多对多关系

    • 多对多这种关系在建立外键字段的时候不能建在这两个表的任何一个或者都建,否则没有办法申请创建这两个表,那么这个时候我们就需要创建第三个表来将这两个表的关系连接到一起实现多对多关系

    create table book(
    	id int primary key auto_increment,
    	title varchar(32)
    );
    create table author(
    	id int primary key auto_increment,
    	name varchar(32)
    );
    create table bookandauthor(
    	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
    );
    

约束条件之一对一

• 前戏(创建一个用户表和一个用户详情表)

  • 站在用户表的角度来看,我有一些经常需要表示的东西,那么我有些信息和另一个表相同那么和他的关系
  • 不可以
  • 站在详情表的角度来看
  • 也不可以
  • 两边都没有那么就需要考虑是不是两张表之间没有关系如果有点关系那么就是一对一关系
  • 针对一对一关系,外键字段建在任何一张表都可以但是建议建立在查询频率高的那张表,方便以后查询

  create table user(
  	id int primary key auto_increment,
  	name varchar(32),
  	detail_id int unique,
  	foreign key(detail_id) references userDetail(id)
  	on update cascade
  	on delete cascade
  );
  create table userDetail(
  	id int primary key auto_increment,
  	phone bigint
  );