外键约束 mysql

外键约束 mysql

让表与表之间产生关系，从而保证数据的正确性

建表时创建外键

create table 表名(
	...
	constraint 外键名称 foreign key （外键列名称） references 主表名称（主表列名称） 
);

例子

-- 建老师表（主表）
CREATE TABLE t3(
id INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(5)
);

-- 建学生表（从表）
CREATE TABLE s3(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(5),
tid INT,
CONSTRAINT fk_st FOREIGN KEY (tid) REFERENCES t3(id)
);

删除外键

alter table 表名 drop foreign key 外键名;

追加外键

alter table 表名 add constraint 外键名 foreign key (外键列名称） references 主表名称（主表关联列）

单词

演练代码

-- 外键关联的演练
-- 建表时创建外键

-- 建库
CREATE DATABASE testForeignKey CHARACTER SET utf8;

-- 选中库
USE testForeignKey;

-- 查看选中
SELECT DATABASE();

-- 建主表，老师表
CREATE TABLE teacher(
-- id int primary key auto_increment,
id INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(5)
);

-- 查看表
SHOW TABLES;

-- 建从表，学生表
-- 学生表中定义一个列作为外键列，起名就叫tid吧
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(5),
tid INT,
-- 格式
-- constraint 外键名称 foreign key(外键列名称) references 主表名称（主表主键名）
CONSTRAINT fk_st FOREIGN KEY(tid) REFERENCES teacher(id)
);

-- 查看表结构
DESC student;


-- 外键演练
-- 在表创建好后追加外键
-- 新建一个班级表 一个老师带多个班级，多个班级由一个老师带
-- 班级表是从表，老师表是主表
-- 在从表中建外键列
-- 先新建一个班级表，假设没有考虑到一些事情，我们通过追加的方式补齐
CREATE TABLE classTab(
id INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(6)
);

-- 查看表结构
DESC classTab;


-- 突然想起来班级表要与老师关联起来
-- 追加老师id列，起名tid
ALTER TABLE classTab ADD tid INT;

-- 查看表结构
DESC classTab;

-- 给tid列添加外键关联
-- 格式
-- alter table 表名 add constraint 外键名 foreign key(外键列名) references 主表名称（主表主键名）
ALTER TABLE classTab ADD CONSTRAINT fk_ct FOREIGN KEY (tid) REFERENCES teacher(id);

-- 查看表结构
DESC classTab;


-- 目标，取消外键
-- 把班级表的老师外键取消掉，让它成为一个普通的列
DESC classTab;

-- 查看建表语句，从建表语句中拿到外键名称
SHOW CREATE TABLE classTab;

-- 丢掉外键
ALTER TABLE classTab DROP FOREIGN KEY fk_ct;

-- 查看建表语句
SHOW CREATE TABLE classTab;

-- 查看表结构
DESC classTab;



-- 外键的影响力
-- 当前学生与老师表是有关联的，往里面添加数据

-- 显示老师表
SELECT * FROM teacher;

-- 添加老师
INSERT INTO teacher VALUES(NULL,'卡卡西'),(NULL,'自来也');

-- 显示学生数据
SELECT * FROM student;

-- 添加学生信息
INSERT INTO student VALUES(NULL,'鸣人',1);

-- 显示所有信息
SELECT * FROM teacher;
SELECT * FROM student;

-- 往学生表中插入一个学生，给它一个不存在的老师id
-- Cannot add or update a child row: a foreign key constraint fails (`testforeignkey/student`, CONSTRAINT `fk_st` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`))
-- 无法插入，这是受到了外键的影响
INSERT INTO student VALUES(NULL,'博人',999);

-- 尝试修改老师表的id
UPDATE teacher SET id = 6 WHERE tname='自来也';

-- 查看老师表
SELECT * FROM teacher;
-- 学生表查看
SELECT * FROM student;

-- 修改一个关联了学生的老师信息试试
-- 1号老师有学生， 1号老师的主键可以改变吗？？？
UPDATE teacher SET id=7 WHERE tname='卡卡西';
-- 防止代码打错，粘个
UPDATE teacher SET id = 7 WHERE tname='卡卡西';

-- 小结，如果老师有关联学生，那么它是无法修改主键id的
-- 报错：Cannot delete or update a parent row: a foreign key constraint fails (`testforeignkey/student`, CONSTRAINT `fk_st` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`))
-- 同理，它也无法删除（关联了学生的老师，无法删除)
DELETE FROM teacher WHERE tname='自来也';
-- 自来也没带学生，他可以被删掉
DELETE FROM teacher WHERE tname='卡卡西';
-- 卡卡西有带学生， 所以他不可以删除

-- 小结
-- 如果主表中的数据行有从表的数据和他关联，那么，这一行不可删除，另外它的主键id无法改变

-- ?? 主键id不可改变，那tname呢？
-- 试一试

UPDATE teacher SET tname='复制忍者卡卡西' WHERE id=1;

-- 查询
SELECT * FROM teacher;

-- 结论
-- 由于主表的值被从表所使用，所以主表的主键不可随意变更
-- 主表的其它列是可以被变更的


-- 最后小结
-- 主表与从表关联后的影响
-- 主键不可改变
-- 非主键可以改变
-- 被关联的主表行不可删除