Deleting when two foreign keys on same field.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Deleting when two foreign keys on same field.

Author

Topic

WaterWolf
Starting Member

24 Posts

Posted - 2011-06-02 : 08:38:28

Hello, I am trying to create a database table that links two records in another table. This is working okay but I have problems with cascade deletes on my foreign keys. A simplified version of the tables looks like this:


CREATE TABLE [dbo].[tOtherTable](
	[OtherTableID] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_OtherTableID] PRIMARY KEY CLUSTERED 
(
	[OtherTableID] ASC
 )
)

GO


CREATE TABLE [dbo].[tConnections](
	[ConnectionID] [bigint] IDENTITY(1,1) NOT NULL,
	[OtherTableFirstID] [bigint] NOT NULL,
	[OtherTableSecondID] [bigint] NOT NULL,
 CONSTRAINT [PK_ConnectionID] PRIMARY KEY CLUSTERED 
(
	[ConnectionID] ASC
 )
)

GO

ALTER TABLE [dbo].[tConnections]  WITH CHECK ADD  CONSTRAINT [FK_tConnections_tOtherTable_OtherTableFirstID] FOREIGN KEY([OtherTableFirstID])
REFERENCES [dbo].[tOtherTable] ([OtherTableID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tConnections]  WITH CHECK ADD  CONSTRAINT [FK_tConnections_tOtherTable_OtherTableSecondID] FOREIGN KEY([OtherTableSecondID])
REFERENCES [dbo].[tOtherTable] ([OtherTableID])
ON DELETE CASCADE
GO

I have two foreign keys in tConnections referencing the same field in tOtherTable. When it tries to create the second foreign key it fails and is says it 'may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'

If I leave the cascade delete out of the second foreign key then it will create okay but if you try to delete from tOtherTable it can't because that violates the foreign key.

Is there anyway I can make this work and still maintain database integrity?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-02 : 08:55:32

You could use a trigger for one of them.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-02 : 09:12:42

From bol
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.

It's the referencing table that is causing the problem (so creating a computed column on the referenced table to take the other foreign key doesn't help :( ).

It should even catch it if you create another table as a copy of tOtherTable.
The only way would be to split tConnections into two tables and use a view to access the joined table.
This would mean the two tables would get different rows deleted but the view would be correct. You could have a job that deletes orphan entries in the two tables.

I would probably go for the trigger.
Actually - I only allow access via SPs so I wouldn't have the issue - just do the deletes in the SP.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources