returning the table that caused a contraint error

HI all,
I have, as per normal, relations setup with delete contraints on the parent
child relation.
Now I have a table that has many such child relations and constraint. As a
result when a user tries to delete the parent, a correct message is
triggered.
My problem is, trying to determine which table is the cause of this
contraint error. I can see the table mentioned in the 547 error that SQL
produces. I was wondering if there is a SP or something that could return
just the table name, that I could use, instead of wading through the error
message.
Thanks
Robert
hi Robert,
Robert Bravery wrote:
> HI all,
> I have, as per normal, relations setup with delete contraints on the
> parent child relation.
> Now I have a table that has many such child relations and constraint.
> As a result when a user tries to delete the parent, a correct message
> is triggered.
> My problem is, trying to determine which table is the cause of this
> contraint error. I can see the table mentioned in the 547 error that
> SQL produces. I was wondering if there is a SP or something that
> could return just the table name, that I could use, instead of wading
> through the error message.
this should be the scenario you mean...
SET NOCOUNT ON
USE tempdb ;
GO
CREATE TABLE dbo.MasterT (
ID int NOT NULL PRIMARY KEY ,
Data varchar(10) NOT NULL
) ;
CREATE TABLE dbo.DetailT (
ID int NOT NULL PRIMARY KEY ,
IdMasterT int NOT NULL
CONSTRAINT FK_MasterT_DetailT
FOREIGN KEY REFERENCES dbo.MasterT (ID),
Data varchar(10) NOT NULL
) ;
GO
INSERT INTO dbo.MasterT VALUES ( 1 , 'value' ) ;
INSERT INTO dbo.MasterT VALUES ( 2 , 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 1 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 2 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 3 , 2, 'value' ) ;
GO
DELETE dbo.MasterT
WHERE ID = 1 ;
GO
DROP TABLE dbo.DetailT , dbo.MasterT ;
all you can get is the error you already noted, 547, which reports the
actual problem indicating the constraint name, the involved table and colum,
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_MasterT_DetailT'. The conflict occurred in database 'tempdb', table
'DetailT', column 'IdMasterT'.
The statement has been terminated.
no other way is allowed/possible...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply