0

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
5
  • 2
    Looks fine to me. Your DBA would refuse to declare a FK is silly.
    – paparazzo
    Jan 1, 2018 at 17:18
  • As an aside, foreign keys with cascading actions use the serializable isolation level to perform the cascading actions. If you truly want to mimic that behavior, you may want to look at using it as well. Jan 1, 2018 at 17:27
  • You are basically trying to rewrite code (+ time + debugging + hassle) that is provided by the server itself. Tell your so-called DBA to find another job, he's useless at the one he's currently doing!
    – Vérace
    Jan 1, 2018 at 17:38
  • 2
    Have your “DBA” do a little reading. TL;DR: foreign keys help performance. Jan 1, 2018 at 17:46
  • Trusted foreign keys definitely help performance; untrusted ones are pretty neutral. Your "DBA" needs a new position or some studying. ALSO, in your TRY/CATCH, you need to check to see if there's a transaction and what the XACT_STATE is before the rollback, and ideally set XACT_ABORT ON before starting the transaction. See learn.microsoft.com/en-us/sql/t-sql/language-elements/… Jan 10, 2018 at 3:25

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.