如你所说,出现下述提示: - 不能创建关系 'FK_tab_UserRight_tab_Mine'。 ODBC 错误: [Microsoft][ODBC SQL Server Driver][SQL Server]将 FOREIGN KEY 约束 'FK_tab_UserRights_tab_Mine' 引入表 'tab_UserRights' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。 [Microsoft][ODBC SQL Server Driver][SQL Server]未能创建约束。请参阅前面的错误信息。
CREATE TABLE [dbo].[tab_Mine] ( [Mine_id] [int] NOT NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[tab_Users] ( [user_id] [int] NOT NULL , [Mine_id] [int] NOT NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[tab_UsersRight] ( [user_id] [int] NOT NULL , [Mine_id] [int] NOT NULL ) ON [PRIMARY] GOALTER TABLE [dbo].[tab_Mine] ADD CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED ( [Mine_id] ) ON [PRIMARY] GOALTER TABLE [dbo].[tab_Users] ADD CONSTRAINT [PK_tab_Users] PRIMARY KEY CLUSTERED ( [user_id] ) ON [PRIMARY] GOALTER TABLE [dbo].[tab_Users] ADD CONSTRAINT [FK_tab_Users_tab_Mine] FOREIGN KEY ( [Mine_id] ) REFERENCES [dbo].[tab_Mine] ( [Mine_id] ) ON DELETE CASCADE ON UPDATE CASCADE GOALTER TABLE [dbo].[tab_UsersRight] ADD CONSTRAINT [FK_tab_UsersRight_tab_Mine] FOREIGN KEY ( [Mine_id] ) REFERENCES [dbo].[tab_Mine] ( [Mine_id] ) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [FK_tab_UsersRight_tab_Users] FOREIGN KEY ( [user_id] ) REFERENCES [dbo].[tab_Users] ( [user_id] ) GO tab_Users和tab_UsersRight表间只能用触发器了。
--可以啊,下面是创建表的例子:create table tab_Mine(Mine_id int primary key)create table tab_Users([user_id] int Primary key ,Mine_id int not null)create table tab_UsersRight([user_id] int not null ,Mine_id int not null)--添加级联更新 ALTER TABLE tab_UsersRight ADD CONSTRAINT FK_tab_UsersRight_tab_Mine FOREIGN KEY ( Mine_id ) REFERENCES dbo.tab_Mine ( Mine_id ) ON UPDATE CASCADE ON DELETE CASCADEALTER TABLE tab_UsersRight ADD CONSTRAINT FK_tab_UsersRight_tab_Users FOREIGN KEY ( [user_id] ) REFERENCES dbo.tab_Users ( [user_id] ) ON UPDATE CASCADE ON DELETE CASCADEgo --删除测试表 drop table tab_UsersRight,tab_Mine,tab_Users
CREATE TRIGGER 名 on tab_Users INSTEAD OF delete AS BEGIN delete tab_UsersRight where [user_id] in (select [user_id] from deleted) delete tab_Users where [user_id] in (select [user_id] from deleted) END
只是tab_Mine与tab_Users 要用触发器,否则造成导致循环或多重级联路径
CREATE TRIGGER 名 on tab_Users INSTEAD OF update AS BEGIN if update([user_id]) begin if (select count(*) from inserted)>1 rollback tran
declare @a int,@b int select @a=[user_id] from inserted select @b=[user_id] from deleted select * into #a from tab_UsersRight where [user_id]=@b delete tab_UsersRight where [user_id]=@b update tab_Users set [user_id]=@a where [user_id]=@b insert tab_UsersRight ([user_id],Mine_id) select @a,Mine_id from #a end END
- 不能创建关系 'FK_tab_UserRight_tab_Mine'。
ODBC 错误: [Microsoft][ODBC SQL Server Driver][SQL Server]将 FOREIGN KEY 约束 'FK_tab_UserRights_tab_Mine' 引入表 'tab_UserRights' 中将导致循环或多重级联路径。请指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其它 FOREIGN KEY 约束。
[Microsoft][ODBC SQL Server Driver][SQL Server]未能创建约束。请参阅前面的错误信息。
[Mine_id] [int] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[tab_Users] (
[user_id] [int] NOT NULL ,
[Mine_id] [int] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[tab_UsersRight] (
[user_id] [int] NOT NULL ,
[Mine_id] [int] NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[tab_Mine] ADD
CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED
(
[Mine_id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[tab_Users] ADD
CONSTRAINT [PK_tab_Users] PRIMARY KEY CLUSTERED
(
[user_id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[tab_Users] ADD
CONSTRAINT [FK_tab_Users_tab_Mine] FOREIGN KEY
(
[Mine_id]
) REFERENCES [dbo].[tab_Mine] (
[Mine_id]
) ON DELETE CASCADE ON UPDATE CASCADE
GOALTER TABLE [dbo].[tab_UsersRight] ADD
CONSTRAINT [FK_tab_UsersRight_tab_Mine] FOREIGN KEY
(
[Mine_id]
) REFERENCES [dbo].[tab_Mine] (
[Mine_id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_tab_UsersRight_tab_Users] FOREIGN KEY
(
[user_id]
) REFERENCES [dbo].[tab_Users] (
[user_id]
)
GO
tab_Users和tab_UsersRight表间只能用触发器了。
,Mine_id int not null)create table tab_UsersRight([user_id] int not null
,Mine_id int not null)--添加级联更新
ALTER TABLE tab_UsersRight ADD CONSTRAINT
FK_tab_UsersRight_tab_Mine FOREIGN KEY
(
Mine_id
) REFERENCES dbo.tab_Mine
(
Mine_id
) ON UPDATE CASCADE
ON DELETE CASCADEALTER TABLE tab_UsersRight ADD CONSTRAINT
FK_tab_UsersRight_tab_Users FOREIGN KEY
(
[user_id]
) REFERENCES dbo.tab_Users
(
[user_id]
) ON UPDATE CASCADE
ON DELETE CASCADEgo
--删除测试表
drop table tab_UsersRight,tab_Mine,tab_Users
INSTEAD OF delete
AS
BEGIN
delete tab_UsersRight where [user_id] in (select [user_id] from deleted)
delete tab_Users where [user_id] in (select [user_id] from deleted)
END
要用触发器,否则造成导致循环或多重级联路径
INSTEAD OF update
AS
BEGIN
if update([user_id])
begin
if (select count(*) from inserted)>1
rollback tran
declare @a int,@b int
select @a=[user_id] from inserted
select @b=[user_id] from deleted
select * into #a from tab_UsersRight where [user_id]=@b
delete tab_UsersRight where [user_id]=@b
update tab_Users set [user_id]=@a where [user_id]=@b
insert tab_UsersRight ([user_id],Mine_id) select @a,Mine_id from #a
end
END
可否给出一个出现上述情况的原因?多谢
Mine_id(primary key)
和
tab_Users:
Mine_id(Foreign key)tab_Users:
user_id(Primary key) 和
tab_UsersRight:
user_id(Foreign key)你只需建立这两个关联就够了