SQL: Delete rows from two tables

Question

I have two tables. Those tables have two relation between them.

Table 1
   * ID_XPTO (PK)
   * Detail

Table 2
   * ID_XPTO (FK) (PK)
   * ID_XPTO2 (FK) (PK)

Those two relations exists. Table 1 -< Table2 Table 1 -< Table2

My question is that I need to delete some row in table 1. I'm doing on that way

declare @table Table (xptoTable2 int)
insert into @table
     select ID_XPTO2
     from Table2 
     where ID_XPTO = @ID_XPTO

delete from Table2
where ID_XPTO = @ID_XPTO

delete from Table
where ID_XPTO in (select xptoTable2from @table)

I know that I could use ON DELETE SET NULL on table2. On that way I could then search for all rows with null value on ID_XPTO2 and delete them, but DBA does not wants to use it.

Is there some better solution to do this process?

Answer 1

Use ON DELETE CASCADE. It'll automatically delete referencing rows.

Answer 2

Why don't you use ON DELETE CASCASE?

DROP TABLE t_f
DROP TABLE t_m
CREATE TABLE t_m (id INT NOT NULL IDENTITY PRIMARY KEY , value VARCHAR(50))
CREATE TABLE t_f (id INT NOT NULL IDENTITY PRIMARY KEY, m INT, CONSTRAINT fk_m FOREIGN KEY (m) REFERENCES t_m(id) ON DELETE CASCADE)
INSERT INTO t_m (value) VALUES ('test')
INSERT INTO t_f (m) VALUES (1)
DELETE FROM t_m
SELECT * FROM t_m
SELECT * FROM t_f

id           value
------------ ------
0 rows selected

id           m
------------ ------
0 rows selected

Answer 3

Two methods I know of:

1. You could use ON DELETE CASCADE

2. Write your SQL to clean up after itself ie:

    DECLARE @DetailCriteria ...
   
   
    SET @DetailCriteria = '....'
   
   
    BEGIN TRAN
    -- First clear the Table2 of any child records
       DELETE FROM Table2 
       WHERE 
         ID_XPTO IN (SELECT ID_XPTO FROM Table1 WHERE Detail = @DetailCriteria)
         OR ID_XPTO2 IN (SELECT ID_XPTO FROM Table1 WHERE Detail = @DetailCriteria)
   
   
    -- Next clear Table2 (which will delete fine because you've followed the referential chain)
       DELETE FROM Table1 WHERE Detail = @DetailCriteria
   
   
    -- commit if you're happy (should check @@ERROR first)
    COMMIT
   

Answer 4

You have these options:

• Delete in two statements, as you are doing now. Delete from Table2 first.

• Delete from two tables in one statement, if your brand of database supports multi-table DELETE syntax (e.g. MySQL). This is not standard SQL, but it is handy.

• Use cascading referential integrity constraints (I understand your DBA has nixed this option).

• Write a trigger BEFORE DELETE on Table1, to delete or set NULL any reference in Table2. Check with your DBA to see if this is any more acceptable than the cascading RI constraints.

Finally, I would advise talking to your DBA and asking the same question you asked here. Find out what solution he/she would prefer you to use. Folks on StackOverflow can answer technical questions, but it sounds like you are dealing with an IT policy question.