MySQL外键约束简单实例

constraint foreign_key_name  foreign key(cloumn_name)
  references database_name(cloumn_name) 
  on update[on delete] [no action] [set null ] 
  [set default] [cascade]

on update 和 on delete 后面可以跟的词语有四个
no action ， set null ， set default ，cascade
no action 表示 不做任何操作，
set null 表示在外键表中将相应字段设置为null
set default 表示设置为默认值(restrict)
cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除

SELECT * FROM springcloud.payment;
insert into payment(serial)  values('serial001');

# 方法一 外键测试删除
create table sys_user(
id bigint(20)auto_increment primary key,
username varchar(50),
password varchar(50)
);
select * from sys_user;
select * from roles;
create table roles(
id int primary key not null,
role varchar(5) not null
);
insert into roles(id,role) value(1,'admin');
insert into roles(id,role) values(2,'user'),(3,'view');
alter table sys_user add roleid int after password;
alter table sys_user add constraint roleid foreign key(roleid) references roles(id);
update sys_user set roleid=4 where id =1;
delete from sys_user where roleid =1;
delete from roles where roles.id=1;

insert into sys_user(id,username,password,roleid) value(1,'admin','000000',1);

# 方法二 级联测试主表删除同时附表删除该记录
select * from employee;
create table employee(
id bigint(20)auto_increment primary key,
empname varchar(50)
);
INSERT INTO employee VALUES(1,'张三软件开发部',1);
INSERT INTO employee VALUES(2,'李四软件开发部',2);
INSERT INTO employee VALUES(3,'王五应用维护部',3);

select * from salary;
# 员工是主表，其他副表有员工的关联属性外键，添加级联效应删除员工其他表一同删除；
# 员工是主表，只有删除了其他关联了的属性才可以删除主表信息；
create table salary(
id bigint(20)auto_increment primary key,
number bigint(20)
);
INSERT INTO salary VALUES(1,5000);
INSERT INTO salary VALUES(2,6000);
INSERT INTO salary VALUES(3,7000);

alter table employee change column stuname empname varchar(30); 
alter table employee change column salaryid salaryid bigint;# 类型修改一致
alter table employee add salaryid int after empname;#添加外键字段
alter table employee add constraint employee_salaryid foreign key(salaryid) references salary(id) on delete cascade;#添加外键约束并进行级联关系

delete from employee where id = 1;

delete from salary where id = 1;# 没有这个人也就没有相应工资表,工资表外键员工id

# 方法三 级联测试外键直接创建约束foreign key
select * from student;
create table student(
id bigint(20)auto_increment primary key,
stuname varchar(50),
resultid bigint(11),
constraint stu_resultid foreign key(resultid) references result(id)
);

select s.id,s.stuname,r.number from student s natural join result r where s.id = r.id;

INSERT INTO student VALUES(1,'张三',1);
INSERT INTO student VALUES(2,'李四',2);
INSERT INTO student VALUES(3,'王五',3);

select * from result;
create table result(
id bigint(20)auto_increment primary key,
number int(5)
);

INSERT INTO result VALUES(1,70);
INSERT INTO result VALUES(2,80);
INSERT INTO result VALUES(3,90);

delete from student where id = 1;

# 方法四 外键约束foreign key
select * from course2;
create table course2(
id bigint(20)auto_increment primary key,
coursename varchar(50),
studentid bigint,
constraint stuid foreign key(studentid) references student2(id)
);

delete from course2 where id=1;

INSERT INTO course2 VALUES(1,'语文',1);
INSERT INTO course2 VALUES(2,'数学',2);
INSERT INTO course2 VALUES(3,'化学',3);

select * from student2;
create table student2(
id bigint(20)auto_increment primary key,
stuname varchar(50)
);


INSERT INTO student2 VALUES(1,'张三');
INSERT INTO student2 VALUES(2,'李四');
INSERT INTO student2 VALUES(3,'王五');

delete from student2 where id = 1;