--作用:如果父表中的记录被删除,则子表中对应该记录的自动被删除/更新
/*语法:
foreign key (column,[,...n])
references referenced_table_name[(ref_column[,...n])]
on delete cascade [on update cascade]
*/
--父表:部门表
DROP TABLE IF EXISTS [Department]
create table Department
( id int primary key,
name nvarchar(20) not null
)
insert Department values
(1,'部门1'),
(2,'部门2'),
(3,'部门3')
--子表:员工表
drop table if exists Employee
create table Employee
( id int primary key,
name nvarchar(20) not null,
dept_id int not null,
--foreign key (dept_id)
--references department(id)
--on delete cascade
foreign key (dept_id) --子表外键
references department(id) --父表主键
on update cascade--更新级联
)
insert Employee values
(1,'员工1',1),
(2,'员工2',1),
(3,'员工3',2),
(4,'员工4',2),
(5,'员工4',3)
--删除(更改)部门表的部门3,则员工表属于部门3的数据也会被删除
--delete a from Department as a
--where id=3
--删除子表员工表(employee)下的属于部门2的所有数据,父表部门表(department)的部门2不会删除
--delete employee from Employee
--where dept_id=1
update Department set id=30 where id=3
select *from Department
select * FROM Employee