How to check foreign key constraint failed for what reason.

Question

0.00/5 (No votes)

See more:

I have created a database in sqlserver with multiple tables and almost each tables are linked with foreign keys. Using PHP CodeIgniter as backend
Now as per general flow, when deleting/updating the record database verifys the foreign keys and if not matched it gives error.
here, what i want to do is to show the user proper message why the foreign key failed. That means, if i am trying to delete from parent table and it's referenced record exists in secondary table, instead of showing long un-understandable messge i want to show message like "You should delete the records from <secondary table="">" first.

My table structure is,
table1 :
<user_master< b="">

Id, name, email,password

Table 1: User_details
id,fk_user_master_id, address,phone

Also, if there are multiple foreign keys avaliable for the same table then i want to show the list of tables which will be conflicting the keys.

If exact records from reference tables which are preventing delete can also be retrieved then it will be great.

What I have tried:

I have successfully retrieved the foreignkeys assigned with the table using below query

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'User_master'

Posted 27-Jul-19 19:04pm

Naikniket

Updated 27-Jul-19 19:23pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-07-27T19:23:00

Foreign keys are there to let SQL ensure that the database never contains "bad" or inconsistent data - it won't let you change information in such a way that a foreign key "points" to a record that no longer exists.

So if you want to delete or change information, you need to delete or change the foreign key row first, then the "target" row.

So if you have an Invoices table, and an InvoiceLines table where each row has a foreign key back to the appropriate Invoice row, then you deleted all the rows from the InvoiceLines table that link to "invoice nnnn" first, then you deleted "invoice nnnn" from the Invoices table.

So first off, all of that should be inside a transaction, so that if you get a failure, you can roll back and not lose half the data and leave the rest.

Second, inside the transaction, use TRY...CATCH (Transact-SQL) - SQL Server | Microsoft Docs[^] to detect the problem, and you can use the error information the CATCH supplies to report it how you want before you ROLLBACK the transaction.