October 27, 2006 at 9:43 am
I have the following scenario
The following hierarchy of records can be created
If record in Table C is tied to Table B, the fk's to Table A will be the same.
So what I had planned on doing was a cascade from Table A to Table B And Table A to Table C. This will only work however if Table C is the first table to cascade.
Can anybody 1) enlighten me on how the order is determined, 2) Give suggestions on how to handle this. (Note: we dont use triggers in our system)
October 27, 2006 at 11:16 am
You cannot set on casade delete for all those relations (circular relations) >>
c to b = fine
b to a = fine
c to a >> cascades on b, which cascades on c which cascasdes back to a.
You'll either have to enforce all deletes through stored procs or start using triggers.
May I ask why your organisation has decided to not use triggers at all?
October 27, 2006 at 11:25 am
Here's a script to show you the error... and give you something to toy with :
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoB_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[DemoB] DROP CONSTRAINT FK_DemoB_DemoA GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoA GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoB]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoB GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoC]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[DemoC] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoB]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[DemoB] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoA]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[DemoA] GO CREATE TABLE [dbo].[DemoA] ( [IDa] [char] (10) COLLATE French_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[DemoB] ( [IDb] [char] (10) COLLATE French_CI_AS NOT NULL , [IDa] [char] (10) COLLATE French_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[DemoC] ( [IDc] [char] (10) COLLATE French_CI_AS NOT NULL , [IDa] [char] (10) COLLATE French_CI_AS NOT NULL , [IDb] [char] (10) COLLATE French_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[DemoA] WITH NOCHECK ADD CONSTRAINT [PK_DemoA] PRIMARY KEY CLUSTERED ( [IDa] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DemoB] WITH NOCHECK ADD CONSTRAINT [PK_DemoB] PRIMARY KEY CLUSTERED ( [IDb] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DemoC] WITH NOCHECK ADD CONSTRAINT [PK_DemoC] PRIMARY KEY CLUSTERED ( [IDc] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DemoB] ADD CONSTRAINT [FK_DemoB_DemoA] FOREIGN KEY ( [IDa] ) REFERENCES [dbo].[DemoA] ( [IDa] ) ON DELETE CASCADE GO ALTER TABLE [dbo].[DemoC] ADD CONSTRAINT [FK_DemoC_DemoA] FOREIGN KEY ( [IDa] ) REFERENCES [dbo].[DemoA] ( [IDa] ), CONSTRAINT [FK_DemoC_DemoB] FOREIGN KEY ( [IDb] ) REFERENCES [dbo].[DemoB] ( [IDb] ) ON DELETE CASCADE GO --EVERYTHING is fine untill you alter this foreign key to cascade on deletes : ALTER TABLE dbo.DemoC DROP CONSTRAINT FK_DemoC_DemoA GO ALTER TABLE dbo.DemoC WITH NOCHECK ADD CONSTRAINT FK_DemoC_DemoA FOREIGN KEY ( IDa ) REFERENCES dbo.DemoA ( IDa ) ON DELETE CASCADE /* Serveur : Msg 1785, Niveau 16, État 1, Ligne 1 Introducing FOREIGN KEY constraint 'FK_DemoC_DemoA' on table 'DemoC' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Serveur : Msg 1750, Niveau 16, État 1, Ligne 1 Could not create constraint. See previous errors. */ --clean up IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoB_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[DemoB] DROP CONSTRAINT FK_DemoB_DemoA GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoA GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoB]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoB GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoC]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[DemoC] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoB]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[DemoB] GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoA]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[DemoA] GO
October 27, 2006 at 11:41 am
I currently have it setup as cascades A -> B and A -> C and restrict B -> C
So when deleting from A, if i am know that sql will always delete from C first and then B, I wont need to wory about the relationship from B -> C.
Scenario
If I delete Table A - record 1, and it cascades to Table C record 1 and record 2 and then Table B record 1 everything is fine.
All Table C records pointing to Table B will be gone prior to to Table B deletes.
However if Table B is the first to cascade, the Restriction on the Table C record will kick in and not allow this.
I am not sure why the decision was made to stay away from triggers.
October 27, 2006 at 12:05 pm
Looks like you'll be ok. The delete doesn't fail (the previous err message was most likely to allow this type of operation not to fail) :
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoB_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DemoB] DROP CONSTRAINT FK_DemoB_DemoA
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoA
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoB]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoB
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoC]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DemoC]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoB]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DemoB]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoA]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DemoA]
GO
CREATE TABLE [dbo].[DemoA] (
[IDa] [char] (10) COLLATE French_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DemoB] (
[IDb] [char] (10) COLLATE French_CI_AS NOT NULL ,
[IDa] [char] (10) COLLATE French_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DemoC] (
[IDc] [char] (10) COLLATE French_CI_AS NOT NULL ,
[IDa] [char] (10) COLLATE French_CI_AS NOT NULL ,
[IDb] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DemoA] WITH NOCHECK ADD
CONSTRAINT [PK_DemoA] PRIMARY KEY CLUSTERED
(
[IDa]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DemoB] WITH NOCHECK ADD
CONSTRAINT [PK_DemoB] PRIMARY KEY CLUSTERED
(
[IDb]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DemoC] WITH NOCHECK ADD
CONSTRAINT [PK_DemoC] PRIMARY KEY CLUSTERED
(
[IDc]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DemoB] ADD
CONSTRAINT [FK_DemoB_DemoA] FOREIGN KEY
(
[IDa]
) REFERENCES [dbo].[DemoA] (
[IDa]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DemoC] ADD
CONSTRAINT [FK_DemoC_DemoA] FOREIGN KEY
(
[IDa]
) REFERENCES [dbo].[DemoA] (
[IDa]
) ON DELETE CASCADE,
CONSTRAINT [FK_DemoC_DemoB] FOREIGN KEY
(
[IDb]
) REFERENCES [dbo].[DemoB] (
[IDb]
)
GO
INSERT INTO dbo.DemoA (IDa) VALUES ('a')
INSERT INTO dbo.DemoB (IDa, IDb) VALUES ('a', 'b')
INSERT INTO dbo.DemoC (IDa, IDb, IDc) VALUES ('a', 'b', 'c')
SELECT * FROM DemoA
SELECT * FROM DemoB
SELECT * FROM DemoC
DELETE FROM dbo.DemoA
SELECT * FROM DemoA
SELECT * FROM DemoB
SELECT * FROM DemoC
--clean up
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoB_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DemoB] DROP CONSTRAINT FK_DemoB_DemoA
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoA]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoA
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_DemoC_DemoB]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[DemoC] DROP CONSTRAINT FK_DemoC_DemoB
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoC]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DemoC]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoB]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DemoB]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DemoA]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DemoA]
GO
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply