delete contraint

17 Feb 2006 2:03 PM

Robert Bravery

HI all,
Hope I have the correct terminology.
I have related tables, with cascading deletes off. In otherwords, you cannot
delete a parent row if there are related child row.
Now when this happens a correct error is produced.
Is there any way to have a single return statement stipulation which child
table was involved in the cascading delete( or rarther the parent delete
failing)
Reasoning behind this is that because I have many child tables related with
a single parent, it would be a lot easier to know which table cause the
delete to fail, go to that table and determin if row in that particular
table can be deleted.
Currently I have to go to every related child table, and figure out if it
caused the delete to fail, and if ti did, then take the needed action.
I'm really looking for principals here, or for someone to steer me in the
right direction, perhaps some reading material etc.

Thanks
Robert

17 Feb 2006 2:02 PM

Tibor Karaszi

Are you saying that you want to know which referencing table is it that prohibits the DELETE
operation? Check out the error message from the DELETE operation. If you have several referencing
tables that has a conflict, you will only get on of them, though:

CREATE TABLE rd(c1 int primary key)
INSERT INTO rd (c1) VALUES(1)
GO
CREATE TABLE rs1(c1 int REFERENCES rd(c1))
CREATE TABLE rs2(c1 int REFERENCES rd(c1))
INSERT INTO rs2 (c1) VALUES(1)
CREATE TABLE rs3(c1 int REFERENCES rd(c1))
INSERT INTO rs3 (c1) VALUES(1)

GO

DELETE FROM rd WHERE c1 = 1

Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__rs2__c1__4D0CD9BB". The conflict
occurred in database "tempdb", table "dbo.rs2", column 'c1'.
The statement has been terminated.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Robert Bravery" <m*@u.com> wrote in message news:egQxAm8MGHA.2828@TK2MSFTNGP12.phx.gbl...
> HI all,
> Hope I have the correct terminology.
> I have related tables, with cascading deletes off. In otherwords, you cannot
> delete a parent row if there are related child row.
> Now when this happens a correct error is produced.
> Is there any way to have a single return statement stipulation which child
> table was involved in the cascading delete( or rarther the parent delete
> failing)
> Reasoning behind this is that because I have many child tables related with
> a single parent, it would be a lot easier to know which table cause the
> delete to fail, go to that table and determin if row in that particular
> table can be deleted.
> Currently I have to go to every related child table, and figure out if it
> caused the delete to fail, and if ti did, then take the needed action.
> I'm really looking for principals here, or for someone to steer me in the
> right direction, perhaps some reading material etc.
>
> Thanks
> Robert
>
>