SQL Server Deleting Rows – How to Delete Rows from Tables Without FK Relationships

sql serversql-server-2012t-sql

I have three tables, one of them is the "master" table and each of the other two has a field that contains the PK of the corresponding row in the master table, something like this:

TblA         TblB           TblC
----         ----           ----
ID_A (PK)    ID_B (PK)      ID_C (PK)
FieldA       ID_A (FK)      ID_A (FK)
...          FieldB         FieldC
             ...            ...

The DBA refused to create FK relationships between these tables ("performance hit" was his explanation). So the FK fields are actually just "normal" fields.

When deleting rows from the master table (TblA), the corresponding rows in the other tables have to be deleted as well. As there are no FK relationships (and therefore no cascading deletion), what is the safest way to delete these rows?

What about this transaction? (@RowID is the ID of the row to be deleted in TblA.)

BEGIN TRANSACTION
BEGIN TRY
   DELETE FROM TblA
   WHERE ID_A = @RowID

   DELETE FROM TblB
   WHERE ID_A = @RowID

   DELETE FROM TblC
   WHERE ID_A = @RowID

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
END CATCH

Best Answer

Your example would allow for the cascading delete that a cascading delete foreign key would. But I would argue that a foreign key does much more than facilitate simple cascading operations. Borrowing from the documentation on FOREIGN KEY Constraints:

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

For example, the Sales.SalesOrderHeader table in the AdventureWorks2008R2 database has a link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. Blockquote

enter image description here

Although the main purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.

A FOREIGN KEY constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

Related Question