Click here to Skip to main content
15,562,816 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 27-Jul-19 20:23pm

1 solution

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.
 
Share this answer
 
Comments
Naikniket 28-Jul-19 1:27am    
Yes you are correct, but here my question is quite different. I want to show user a customized message if the foreign key constraint fails. That means while deleting a record, if the reference of that record exists in secondary table a message saying you have <number_of> record in <secondary_table_name>, delete it first.
OriginalGriff 28-Jul-19 1:48am    
Yes - and you use the error information to do that, it's all you have to work with!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900