人生第一个MYSQL外键(Foreign Key)的使用

西湖鱼

外键笔记
定义：如果表A的主关键字是表B中的字段，则此字段称为表B的外键，表A称为主表，表B称为从表。
1.用户组表
create table t_group (
id int not null,
name varchar (10),
primary key (id)
);
insert into t_group values(1,'Group1');
insert into t_group values(2,'Group2');
2.级联方式(CASCAEDE)
创建用户表
create table t_user(
id int not null,
name varchar(10),
groupId int,
primary key(id),
FOREIGN KEY (groupid) REFERENCES t_group (id) ON DELETE CASCADE ON UPDATE CASCADE
);
2.1参照完整性测试
INSERT INTO t_user VALUES(1,'zhang',1);--可以插入
INSERT INTO t_user VALUES(2,'song',2);--可以插入
INSERT INTO t_user VALUES(3,'ma',3);--错误，用户组3不存在,与参照完整性不符
2.2约束方式测试
INSERT INTO t_user VALUES(7,'zhu',1);
INSERT INTO t_user VALUES(8,'hu',2);
INSERT INTO t_user VALUES(9,'wen',2);
DELETE FROM t_group WHERE id=2;--删除用户组的2时，此时用户中的id:8与9同时删除
UPDATE t_group SET id=100 WHERE id=1;--更新用户组的id:1时，此时用户中的groupId也变成了100
3.置空方式(SET NULL)
创建用户表
CREATE TABLE t_user3(
id INT NOT NULL,
name VARCHAR(10),
groupId INT,
PRIMARY KEY(id),
FOREIGN KEY (groupId) REFERENCES t_group(id) ON DELETE SET NULL ON UPDATE SET NULL
);
INSERT INTO t_group VALUES(4,'Group4');
INSERT INTO t_group VALUES(5,'Group5');
INSERT INTO t_group VALUES(6,'Group6');
3.1参照完整性测试
INSERT INTO t_user3 VALUES (1,'Jack',4);--可以插入
INSERT INTO t_user3 VALUES (11,'Mary',5);--可以插入
INSERT INTO t_user3 VALUES (12,'Jack',9);--错误，用户组9不存在，与参照完整性不符
3.2约束性测试
INSERT INTO t_user3 VALUES (20,'Kevein',4);
INSERT INTO t_user3 VALUES (21,'Obama',5);
INSERT INTO t_user3 VALUES (22,'Lisha',5);
DELETE FROM t_group WHERE id=5;--此时，t_user中的id:21,22中的groupId被设置成为了NULL
UPDATE t_group SET id=400 WHERE id=4;--此时，t_user中的id:20中的groupId被设置成为了NULL
4.禁止方式(NO ACTION/RESTRICT)
创建用户表
CREATE TABLE t_user5(
id INT NOT NULL,
name VARCHAR(10),
groupId INT,
PRIMARY KEY(id),
FOREIGN KEY (groupId) REFERENCES t_group(id) ON DELETE NO ACTION ON UPDATE NO ACTION
);
4.1参照性测试
INSERT INTO t_user5 VALUES(1,'WANG',100);--ok
INSERT INTO t_user5 VALUES(2,'WAN',400);--ok
INSERT INTO t_user5 VALUES(3,'W',700);--error与参照性不符
4.2约束性测试
INSERT INTO t_user5 VALUES(10,'WANG',100);
INSERT INTO t_user5 VALUES(11,'WAN',400);
INSERT INTO t_user5 VALUES(12,'WAN',400);
DELETE FROM t_group WHERE id='400';--错误，因为从表中有相关的引用，主表不能删除
UPDATE t_group SET id='4000' WHERE id='400';--错误，因为从表中有相关的引用，主表不能删除