Clever Geek Handbook

Can I display conflicting primary key when receiving an error: DELETE statement was in conflict with REFERENCE constraint

I have a problem deleting a row in SQL Server 2008 due to a primary key constraint:

DELETE statement conflicts with REFERENCE ...

Is there a way to get the conflicting primary key displayed in the error message?

+3


source to share


3 answers


There is nothing built into SQL Server (or competing products) that will tell you which particular table and row is causing the conflict. The error message only reports FK for verification.

Based on the answer JLo gave , you can create your own [on before drop] trigger for your tables, which will traverse each FK table and count related records. If you are using SP you can use a try / catch block to do the same.



You have my sympathy because it would be quite tedious to write something like this. If you've done this for more than a few tables, I would recommend using a code generator (T4 toolkit or MyGeneration) to generate them for you.

+2


source


This will give you foreign keys for a specific table. You will need to test each of the joints yourself.

SELECT
    f.name as [Foreign Key],
    OBJECT_NAME(f.parent_object_id) as [Table],
    COL_NAME(fc.parent_object_id, fc.parent_column_id) as [Column],
    OBJECT_NAME(f.referenced_object_id) as [Reference Table],
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) as [Reference Column]
FROM sys.foreign_keys f
JOIN sys.foreign_key_columns fc
    ON fc.constraint_object_id = f.object_id
WHERE fc.parent_object_id = OBJECT_ID(N'[dbo].[Document]')


This should get you started ... good luck!

+1


source


The value that the constraint receives is one of the values ​​you are trying to remove, so ex.

delete from tableA where value = 123

There would be a constraint error if a foreign key constraint existed somewhere in another table with the value 123 ... which is why it's correct to name your constraints, but if not, you can always run a J LO script to find out what constraints are available.

Something more complex like

delete from tableA where value IN (select top 10 * from tableA)

Change your delete to select * from and cross-reference those values ​​using an IN statement similar to the one above ... you can also run my subquery directly, but this is just an example.

0


source






More articles:


All Articles