外键约束

外键约束

1.什么是外键：

外键就是用来两个表数据之间建立连接，可以使一个列或多个列，一个表可以有一个或多个外键。

外键时表的一个字段，不是表的主键，但对应另一个表的主键

外键对应的是参照完整性，一个表的外键可以使空值，若不为空，则每一个外键的值必须等于另一个表中主键的某个值。

2.外键作用：保持数据的一致性，完整性。

一对多

凡是单向多对一的表关系，称之为一对多的外键关系。

语法：foreign key（当前表中建立关系的外键字段）references 被关联表名（id）

创建被关联表：
create table dep(id int primary key auto_increment,dep_name varchar(16),dep_desc varchar(255) );
创建关联表：
create table emp(id int primary key auto_increment,name varchar(16),age int,gender enum('male', 'female', 'others') default 'male',dep_id int not null,foreign key(dep_id) references dep(id) );
向被关联表插入数据：
insert into dep(dep_name, dep_desc) values('nb_外交部', '国际形象大使部门'),('sb_教学部', '造程序员部门!!!!'),('技术部', '技术有限部门');
向关联表插入数据：
insert into emp(name, age, gender, dep_id)values('tank', 17, 'male', 1), ('jason', 70, 'male', 2),('sean', 50, 'male', 2),('egon', 88, 'male', 2),('owen', 95,'female', 3);
插入错误的会报错：因为并没有999部门
insert into emp(name, age, gender, dep_id) values('大饼', 100, 'others', 999);
更新数据时需要先删除已关联的dep_id字段，才能修改dep表中的关联id字段。
delete from emp where dep_id=1;
update dep set id=100 where id=1;
删除数据时需要先删除关联表中的记录，再删除被关联表中的数据。
delete from emp where dep_id=2;
 delete from dep where id=2;

级联更新与级联更新

on update casede

on delete casecade

创建被关联表：
create table dep1(id int primary key auto_increment,dep_name varchar(16),dep_desc varchar(255) );
创建关联表：
create table emp1(id int primary key auto_increment,name varchar(16),age int,gender enum('male', 'female', 'others') default 'male',dep_id int not null,foreign key(dep_id) references dep1(id) on update cascade on delete cascade);
向被关联表插入数据：
insert into dep1(dep_name, dep_desc) values('nb_外交部', '国际形象大使部门'),('sb_教学部', '造程序员部门!!!!'),('技术部', '技术有限部门');
向关联表插入数据：
insert into emp1(name, age, gender, dep_id)values('tank', 17, 'male', 1), ('jason', 70, 'male', 2),('sean', 50, 'male', 2),('egon', 88, 'male', 2),('owen', 95,'female', 3);
跟新数据或删除数据：
更新记录
        update dep2 set id=200 where id=1;
删除记录
        delete from dep2 where id=200;

多对多

创建book表
 create table book(id int primary key auto_increment,title varchar(20),price int,book_content varchar(255));
创建author表
create table author(id int primary key auto_increment,name varchar(16),age int);
创建共有表通过共有表联系两个表
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);
插入数据：
 insert into book(title,price,book_content)values('紫色',20,'讲述宴请小故事'),('爱格',15,'讲述清新小故事'),('格林 童话',50,'讲述睡前小故事');
 insert into author(name,age)values('小编',25),('垃圾小编',25),('安徒生',68);
更新或删除
update book set price=500 where id = 3;
delete from book where id = 1;
​
​

一对一

两张表之间的关系一一对应，将一张张数据量比较大的表拆分成两张表
创建被关联表
create table customer(id int primary key auto_increment,name varchar(16),media varchar(32));
创建关联表#外键必须设为唯一
create table students(id int primary key auto_increment,addr varchar(255),phone char(11),id_card char(18),customer_id int unique,foreign key(customer_id)references customer(id) on update cascade on delete cascade);
插入数据
 insert into customer(name,media)values('hyc','fackbook'),('zsb','ig'),('hb','vk');
  insert into students(addr,phone,id_card,customer_id)values('上海','13833333333','444444444444444444',1），('beijing','11111111111','111111111111111111',2);
​

了解:

• 修改表的操作

  • 语法： 注意: mysql 关键字不区分大小写

    1. 修改表名 ALTER TABLE 表名 RENAME 新表名;

    2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; # 添加到最后一列

       ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 添加到第一列

       ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 添加到某一列之后

    3. 删除字段 ALTER TABLE 表名 DROP 字段名;

    4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; # 修改数据类型

       ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; # 修改字段名，保留字段类型

       ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…]; # 修改字段名与字段类型

• 复制表的操作: 复制表结构＋记录 （key不会复制: 主键、外键和索引） mysql> create table new_service select * from service;

  只复制表结构

  将select * from service where 1=2; ---> 不要真实数据，需要表结构

  mysql> create table new_customer select * from customer where 1=2;