Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk.
Let two constraints on the t3 table, each one with "on delete cascade", if I do:

delete from t1 where id1=$id1;

I want to delete all rows of the t3 which contain the fk id1 and all rows of the t2 which contain the fk id2.
Is it possible in one query ?
Edit: here are my tables:

create T1 (
id1 ...
...
CONSTRAINT pk_id1 PRIMARY KEY (id1) );

create T2 (
id2 ...
 ...
CONSTRAINT pk_id2 PRIMARY KEY (id2) );

create T3 (
id1 ...,
id2 ...,
CONSTRAINT pk_T3 PRIMARY KEY (id1, id2),
CONSTRAINT fk_T3_1 FOREIGN KEY (id2)
REFERENCES T2 (id2) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_T3_2 FOREIGN KEY (id1)
REFERENCES T1(id1) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE )
share|improve this question
    
Which RDBMS are you using? –  Thomas Stringer Jun 10 '13 at 15:17
    
my question was general but I am using postgresql 9.1 ! –  Bertaud Jun 10 '13 at 15:18
    
@Bertaud oops .. I posted my answer with SQL Server in mind ..but Postgresql should have similar thing inplace. –  Kin Jun 10 '13 at 15:28
    
So you have two values $id1, $id2 and you want to DELETE from t1 where id1=$id1; DELETE from t2 where id2=$id2; and all the related rows deleted from t3 (which should be as you have cascade delete enabled)? –  ypercube Jun 10 '13 at 15:41
    
@Bertaud I have deleted my answer, as I work with SQL Server and SybaseASE. Cheers ! –  Kin Jun 10 '13 at 15:56

1 Answer 1

up vote 3 down vote accepted

It seems you want these two statements inside a transaction:

DELETE FROM t2
WHERE EXISTS
      ( SELECT 1
        FROM t3
        WHERE t3.id2 = t2.id2
          AND t3.id1 = $id1
      ) ;

DELETE FROM t1
WHERE t1.id1 = $id1 ;

The cascading delete effects will take care of the rest. Not sure though what locking or isolation level will be best for this operation.

Note that if you specify $id1 = 1, the above will delete all rows from t3 that have id1 = 1 and some rows that do not. If for example, you have 3 rows in t3 with (1,5), (1,6), (1,7), the operation will delete them and also delete all rows that have id2=5 or id2=6 or id2=7. So rows with (4,5), (8,5), (28,5), (2,6), (6,6), (40,7) will also be deleted due to the cascading effects, when the three rows of t2 (with id2 5,6,7 are deleted).

share|improve this answer
    
t2.id1 doesn't exist ??? and how to evict the described effect ? –  Bertaud Jun 10 '13 at 16:55
    
My mistake. The line has been corrected from AND t2.id1 = $id1 to AND t3.id1 = $id1 –  ypercube Jun 10 '13 at 19:19
    
Ok but I don't see the utility of the delete cascade. your first query deletes in T2 and T3 and the second query deletes in T1. Shall I remove on delete cascade and replace it by on delete restrict ? –  Bertaud Jun 10 '13 at 20:57
    
The 2nd query deletes from t3, too, due to the cascades. –  ypercube Jun 10 '13 at 21:10
    
Yes, now I understood. Thank you very much. Do you know sites where I could learn more ? queries on multiple tables are not evident and I thought to understand the cascade ;-) –  Bertaud Jun 10 '13 at 21:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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