question

ruancra avatar image
ruancra asked

Deleting from tables in sequence, based on Foreign key relationship

Hi guys We have a table containing the names of tables, with a procedure that loops through the table names and deletes data, problem is we are getting foreign key errors, as some of the tables are linked via foreign key to other tables. Is there a way that the proc that does the delete, can first check if there are any dependency tables linked to the table names, and first delete from them? Thanks
tsqlforeign-keydevelopmentsql2008
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JohnM avatar image
JohnM answered
Yes, you should able to use the sys.foreign_keys view to determine tables the FK references and then handle accordingly. Reference: https://msdn.microsoft.com/en-us/library/ms189807.aspx Hope this helps!
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just as a secondary thought, it might be better to determine the order and then adjust your table to include the order. Don't know how many tables you have to run through but FK's probably usually won't change in a stable database. You might just get better performance this way then trying to dynamically figuring out the order on every run. Just a thought.
0 Likes 0 ·
Thanks John, i manged to find a query that selects from that view, but not quite getting the order right for deleteing from the tables, here is the query: SELECT o1.name AS FK_table, c1.name AS FK_column, fk.name AS FK_name, o2.name AS PK_table, c2.name AS PK_column, pk.name AS PK_name, fk.delete_referential_action_desc AS Delete_Action, fk.update_referential_action_desc AS Update_Action --INTO #TempTable FROM sys.objects o1 INNER JOIN sys.foreign_keys fk ON o1.object_id = fk.parent_object_id INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id INNER JOIN sys.columns c1 ON fkc.parent_object_id = c1.object_id AND fkc.parent_column_id = c1.column_id INNER JOIN sys.columns c2 ON fkc.referenced_object_id = c2.object_id AND fkc.referenced_column_id = c2.column_id INNER JOIN sys.objects o2 ON fk.referenced_object_id = o2.object_id INNER JOIN sys.key_constraints pk ON fk.referenced_object_id = pk.parent_object_id AND fk.key_index_id = pk.unique_index_id --WHERE --o2.name IN (Select distinct TableName from dbo.tbl_WidgetData) ORDER BY o1.name, o2.name, fkc.constraint_column_id
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.