笔记:设置外键FOREIGN KEY

-----------------------------------在建表时设置了外键----------------------------------------
创建主表tb_train_fk11:
CREATE TABLE IF NOT EXISTS tb_train_fk11(
cid TINYINT UNSIGNED KEY AUTO_INCREMENT,
cname VARCHAR(5) NOT NULL UNIQUE,
population INT UNSIGNED NOT NULL,
area FLOAT(5,2)
)AUTO_INCREMENT=101 ENGINE=INNODB CHARSET=UTF8;
INSERT tb_train_fk11(cname,population,area) VALUES
('魏国',2580,786.24),
('蜀国',1200,295.18),
('吴国',1845,513.72);
创建子表tb_train_fk12:
CREATE TABLE IF NOT EXISTS tb_train_fk12(
userid INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL UNIQUE,
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女') NOT NULL,
country_id TINYINT UNSIGNED,
CONSTRAINT 11_fk_12 FOREIGN KEY(country_id) REFERENCES tb_train_fk11(cid)
)ENGINE=INNODB CHARSET=UTF8;
INSERT tb_train_fk12(username,age,sex,country_id) VALUES
('张飞',36,'男',102),
('刘备',48,'男',102),
('孙尚香',28,'女',103),
('甄姬',30,'女',101),
('黄月英',28,'女',102),
('夏侯惇',35,'男',101),
('黄忠',40,'男',102),
('张馨',26,'女',101),
('郭嘉',24,'男',101),
('太史慈',32,'男',103),
('小乔',30,'女',103),
('赵子龙',22,'男',102),
('曹羡',34,'女',101),
('大乔',31,'女',103),
('周瑜',38,'男',103),
('曹操',50,'男',101),
('关凤',25,'女',102),
('马超',28,'男',102);

注意：由于有外键的约束，直接删除主表tb_train_fk11中的记录(如cid=101)
DELETE FROM tb_train_fk11 WHERE cid=101;
WARING:Cannot delete or update a parent row: a foreign key constraint fails

方法：先删除子表tb_train_fk12中cid=101的记录,在删除主表tb_train_fk11中的记录cid=101
DELETE FROM tb_train_fk12 WHERE country_id=101;
DELETE FROM tb_train_fk11 WHERE cid=101;
SELECT * FROM tb_train_fk11;
SELECT * FROM tb_train_fk12;
 

-----------------------------------在建表后设置外键----------------------------------------
主表：
CREATE TABLE IF NOT EXISTS tb_train_fk11(
cid TINYINT UNSIGNED KEY AUTO_INCREMENT,
cname VARCHAR(5) NOT NULL UNIQUE,
population INT UNSIGNED NOT NULL,
area FLOAT(5,2)
)AUTO_INCREMENT=101 ENGINE=INNODB CHARSET=UTF8;
INSERT tb_train_fk11(cname,population,area) VALUES
('魏国',2580,786.24),
('蜀国',1200,295.18),
('吴国',1845,513.72);
子表：
CREATE TABLE IF NOT EXISTS tb_train_fk12(
userid INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL UNIQUE,
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女') NOT NULL,
country_id TINYINT UNSIGNED
)ENGINE=INNODB CHARSET=UTF8;
INSERT tb_train_fk12(username,age,sex,country_id) VALUES
('张飞',36,'男',102),
('刘备',48,'男',102),
('孙尚香',28,'女',103),
('甄姬',30,'女',101),
('黄月英',28,'女',102),
('夏侯惇',35,'男',101),
('黄忠',40,'男',102),
('张馨',26,'女',101),
('郭嘉',24,'男',101),
('太史慈',32,'男',103),
('小乔',30,'女',103),
('赵子龙',22,'男',102),
('曹羡',34,'女',101),
('大乔',31,'女',103),
('周瑜',38,'男',103),
('曹操',50,'男',101),
('关凤',25,'女',102),
('马超',28,'男',102);

添加外键：ALTER TABLE tb_train_fk12 ADD CONSTRAINT 11_fk_12 FOREIGN KEY(country_id) REFERENCES tb_train_fk11(cid);
删除外键：ALTER TABLE tb_train_fk12 DROP FOREIGN KEY 11_fk_12;


-----------------------------------外键约束----------------------------------------
外键约束的参照操作:
-CASCADE:删除更新主表记录时自动删除更新子表中匹配的行
-SET NULL:主表删除更新行,并设置子表中的外键列为NULL(前提是保证子表中数据不是NOT NULL)
-RESTRICT:拒绝对主表进行删除或更新记录
-NO ACTION:与RESTRICT相同

------测试CASCADE------
创建主表tb_department
CREATE TABLE IF NOT EXISTS tb_department(
dId INT UNSIGNED KEY AUTO_INCREMENT,
dName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT tb_department(dName) VALUES
('数学院'),
('经济院'),
('化学院'),
('物理院'),
('文学院'),
('机械学院');
创建子表tb_stu
CREATE TABLE IF NOT EXISTS tb_stu(
userId INT UNSIGNED KEY AUTO_INCREMENT,
userName VARCHAR(10) NOT NULL,
dId INT UNSIGNED NOT NULL,
CONSTRAINT emp_stu FOREIGN KEY(dId) REFERENCES tb_department(dId) ON DELETE CASCADE ON UPDATE CASCADE
)AUTO_INCREMENT=2018000 ENGINE=INNODB;
INSERT tb_stu(userName,dId) VALUES
('路飞',5),
('汉库克',2),
('鹰眼',1),
('多佛朗明哥',3),
('巴基',2),
('佐罗',4),
('罗宾',6),
('达斯琪',1),
('赤犬',3),
('青雉',5),
('红发',1),
('黄猿',4);
其中:
外键约束:ON DELETE CASCADE:删除主表同时删除子表中相关的记录
ON UPDATE CASCADE:更新主表同时更新子表中相关的记录

DELETE FROM tb_department WHERE dId=1;
SELECT * FROM tb_department;
SELECT * FROM tb_stu;


------测试RESTRICT------
先删除主表和子表:
DROP TABLE tb_stu[必须先删除子表,否则主表不能删除]
DROP TABLE tb_stu

创建主表tb_department
CREATE TABLE IF NOT EXISTS tb_department(
dId INT UNSIGNED KEY AUTO_INCREMENT,
dName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT tb_department(dName) VALUES
('数学院'),
('经济院'),
('化学院'),
('物理院'),
('文学院'),
('机械学院');
创建子表tb_stu
CREATE TABLE IF NOT EXISTS tb_stu(
userId INT UNSIGNED KEY AUTO_INCREMENT,
userName VARCHAR(10) NOT NULL,
dId INT UNSIGNED NOT NULL,
CONSTRAINT emp_stu FOREIGN KEY(dId) REFERENCES tb_department(dId) ON DELETE RESTRICT ON UPDATE RESTRICT
)AUTO_INCREMENT=2018000 ENGINE=INNODB;
INSERT tb_stu(userName,dId) VALUES
('路飞',5),
('汉库克',2),
('鹰眼',1),
('多佛朗明哥',3),
('巴基',2),
('佐罗',4),
('罗宾',6),
('达斯琪',1),
('赤犬',3),
('青雉',5),
('红发',1),
('黄猿',4);
其中:
外键约束:ON DELETE RESTRICT:拒绝对主表进行删除操作
ON UPDATE RESTRICT:拒绝对主表进行更新操作

DELETE FROM tb_department WHERE dId=2;
WARNING:Cannot delete or update a parent row: a foreign key constraint fails