ITTECH123


By: IT In: microsoft.public.sqlserver.programming
I have 2 tables A and Table B respectively:

Table A:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[aa]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[aa](
	[aa1] [int] NOT NULL,
	[aa2] [int] NULL,
	[aa3] [int] NULL,
 CONSTRAINT [PK_aa] PRIMARY KEY CLUSTERED 
(
	[aa1] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END


Table B:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[bb]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[bb](
	[bb1] [int] NOT NULL,
	[bb2] [int] NOT NULL,
	[bb3] [int] NOT NULL,
 CONSTRAINT [PK_bb] PRIMARY KEY CLUSTERED 
(
	[bb1] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO



IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = 
OBJECT_ID(N'[dbo].[FK_bb_aa]') AND parent_object_id = 
OBJECT_ID(N'[dbo].[bb]'))
ALTER TABLE [dbo].[bb]  WITH CHECK ADD  CONSTRAINT [FK_bb_aa] FOREIGN 
KEY([bb2])
REFERENCES [dbo].[aa] ([aa1])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[bb] CHECK CONSTRAINT [FK_bb_aa]


 

IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = 
OBJECT_ID(N'[DBO].[FK_BB_AA1]') AND PARENT_OBJECT_ID = 
OBJECT_ID(N'[DBO].[BB]'))
ALTER TABLE [DBO].[BB]  WITH CHECK ADD  CONSTRAINT [FK_BB_AA1] FOREIGN 
KEY([BB3])
REFERENCES [DBO].[AA] ([AA1])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [DBO].[BB] CHECK CONSTRAINT [FK_BB_AA1]

creating FK_BB_AA1 fails with the following error:

Msg 1785, Level 16, State 0, Line 3
Introducing FOREIGN KEY constraint 'FK_bb_aa1' on table 'bb' may cause 
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO 
ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.
Msg 4917, Level 16, State 0, Line 1
Constraint 'FK_bb_aa1' does not exist.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.



How to get this working.



Re: Cascade Issue

Qian Liang - MSFT


This is not allowed in SQL Server.

The tree of cascading referential actions must not have more than one path to any specified table. If the script succeeded, a single update/delete on table [AA] may cause cascading referential actions to table [BB] through both foreign keys [FK_bb_aa] and [FK_BB_AA1].

You need to specify ON DELETE NO ACTION or ON UPDATE NO ACTION on [FK_bb_aa] before creating the foreign key [FK_BB_AA1].






Re: Cascade Issue

Louis Davidson

You will have to build a trigger to perform the cascading operations instead of using constraints. You can possibly use an instead of trigger and leave the constraints, depending on your needs, or you will need after triggers, in which case you have to code a trigger for the child and parent table (on the child table to check for valid parent values).

Or, if using stored procedures, you might just want to do the cascades manually in the procedure (especially if you don't do a lot of deleting in different procedures.)

If you need help with the triggers just ask back and one of us can help out...







Re: Cascade Issue

Dimitar Bosevski

I need help with triggers. Well I think..
Actually I was using Access database, with ON DELETE, and ON UPDATE actions set Cascade.
There were defined indexes for the fields involved.

Now I'm on SQL server 2005. I define same indexes, but I'm getting this message:
Introducing FOREIGN KEY constraint 'FK_PosSub_Rec' on table 'PosSub' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I read about using triggers instead constrains. But actually i don't know how