Menu
You cannot delete a row if any row is referencing the row to delete via a FK.
Is it possible to know if any row is referencing the row to delete before executing a DELETE statement?
327
DELETE FROM table_name WHERE id = id_to_delete; -- Deleting a record from a table that has foreign key reference. SET FOREIGN_KEY_CHECKS = 1; -- Enabling foreign key checks after running the above query.
To view the objects on which a table depends. In Object Explorer, expand Databases, expand a database, and then expand Tables. Right-click a table, and then click View Dependencies.
To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.
This script will show all the tables that have rows that reference the row you are trying to delete:
declare @RowId int = 1
declare @TableName sysname = 'ParentTable'
declare @Command varchar(max)
select @Command = isnull(@Command + ' union all ', '') + 'select ''' + object_name(parent_object_id) +
''' where exists(select * from ' + object_name(parent_object_id) + ' where ' + col.name+ ' = ' + cast(@RowId as varchar) + ')'
from sys.foreign_key_columns fkc
join sys.columns col on
fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
where object_name(referenced_object_id) = @TableName
execute (@Command)
Assumption that foreign key is not composite.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
