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
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