在线等待！！！又见表关联（主/外键）,请大侠指教，多谢

xlfd2005 2010-07-31 11:13:19

--权限表

CREATE TABLE TBL_CFG_LIMITS
(
RIGHT_ID INT,
RIGHT_NAME VARCHAR(30),
RIGHT_DESC VARCHAR(100)

CONSTRAINT PK_TBL_CFG_LIMITS_RIGHT_ID PRIMARY KEY(RIGHT_ID)
)

--角色表

CREATE TABLE TBL_CFG_ROLES
(
ROLE_ID INT,
ROLE_NAME VARCHAR(30),
ROLE_DESC VARCHAR(100)

CONSTRAINT PK_TBL_CFG_ROLES_ROLE_ID PRIMARY KEY(ROLE_ID)
)

--权限角色关联表
CREATE TABLE TBL_CFG_ROLESLIMITS
(
ID INT IDENTITY(1,1),
ROLE_ID INT,
RIGHT_ID INT

)

权限关联表中可以设置两个外键分别于权限表，角色表的主键关联吗，同时实现级联删除与更新，请大侠指教,多谢

...全文

101 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

duanzhi1984 2010-07-31

打赏
举报

楼上的是正解了...

永生天地 2010-07-31

打赏
举报

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



*/





本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xys_777/archive/2010/07/26/5766742.aspx