[SQL Server] [basic] multi-Table Association Creation

If object_id ('tablecd') is not null drop table tablecd --- add an intermediate table for multiple to multiple pairs
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', 'Company 1'
Go
Create Table tableb (BID varchar (10) primary key, bname varchar (20), Aid varchar (10) References tablea (AID ))
Insert tableb
Select 'b1 ', 'department 1', 'a1' Union all
Select 'b2', 'department 2', 'a1'
Go
Create Table tablec (CID varchar (10) primary key, cname varchar (20), bid varchar (10) References tableb (BID ))
Insert tablec
Select 'c1 ', 'personnel 1', 'b1' Union all
Select 'c2 ', 'staff 2', 'b1' Union all
Select 'c3', 'personnel 3', 'b2' Union all
Select 'c4 ', 'personnel 4', 'b2'
Go
Create Table tabled (did varchar (10) primary key, dname varchar (20 ))
Insert tabled
Select 'd1 ', 'permission 1' Union all
Select 'd2 ', 'permission 2' Union all
Select 'd3 ', 'permission 3' Union all
Select 'd4 ', 'permission 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 = '1'

Select * From tablecd

/*

(One row is affected)

(2 rows affected)

(Four rows affected)

(Four rows affected)

(Four rows affected)

(One row is affected)
Cdid Cid did
-------------------------------
3 C2 d1
4 C3 D4

(2 rows affected)

*/