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