[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 行受影響)

*/