[sql server][基础] 多表关联创建

if object_id('tableCD') is not null drop table tableCD---多对多要加个中间表
go
if object_id('tableD') is not null drop table tableD
go
if object_id('tableC') is not null drop table tableC
go
if object_id('tableB') is not null drop table tableB
go
if object_id('tableA') is not null drop table tableA
go
create table tableA (Aid varchar(10) primary key,Aname varchar(20))
insert tableA select 'A1','公司1'
go
create table tableB (Bid varchar(10) primary key,Bname varchar(20),Aid varchar(10) references tableA(Aid)  )
insert tableB
select 'B1','部门1','A1' union all
select 'B2','部门2','A1'
go
create table tableC (Cid varchar(10) primary key,Cname varchar(20),Bid varchar(10) references tableB(Bid)   )
insert tableC
select 'C1','人员1','B1' union all
select 'C2','人员2','B1' union all
select 'C3','人员3','B2' union all
select 'C4','人员4','B2'
go
create table tableD (Did varchar(10) primary key,Dname varchar(20))
insert tableD
select 'D1','权限1' union all
select 'D2','权限2' union all
select 'D3','权限3' union all
select 'D4','权限4'
go
create table tableCD (CDid int identity primary key,Cid varchar(10) references tableC(Cid)on delete cascade ,Did varchar(10) references tableD(Did)on delete cascade )
insert tableCD
select 'C1','D1' union all
select 'C1','D2' union all
select 'C2','D1' union all
select 'C3','D4'

delete tablec where cid='c1'
--or
--delete tabled where did='d1'

select * from tablecd

/*

(1 行受影响)

(2 行受影响)

(4 行受影响)

(4 行受影响)

(4 行受影响)

(1 行受影响)
CDid        Cid        Did
----------- ---------- ----------
3           C2         D1
4           C3         D4

(2 行受影响)

*/