Make db remove entry in different table when removing entry in one table

Question

I have two tables, t1:

id,field

t2:

id,field,t1_id

t1 and t2 are connected via t1_id

Can I make the db remove the t2 entry automatically when I remove the t1 entry?

If you can drop and create them... http://stackoverflow.com/a/511390/2592994 — calebboyd, Dec 07 '13 at 03:54

score 1 · Accepted Answer · answered Dec 07 '13 at 03:47

1

If you're running at least MySQL 5, how about:

DELIMITER $$
CREATE TRIGGER t1_AD AFTER DELETE ON t1 FOR EACH ROW
BEGIN
    DELETE FROM t2 WHERE t1_id=OLD.id;
END $$
DELIMITER ;

(Caveat emptor: not at my workstation, have not tested!)

answered Dec 07 '13 at 03:47

bishop

What is OLD.id? Are you sure it references to the "old" t1 row that is not existing anymore? Do I run this once? If I want to, how do I make it inactive? – Gewinn Dec 07 '13 at 22:38
Per MySQL docs, [OLD refers to a column of an existing row before it is deleted](http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html). Yes, you run this once. After you run this, the database will run the code between BEGIN and END every time a row in t1 is deleted. If you want to make it inactive, you can use a session variable and an IF test around the DELETE. – bishop Dec 09 '13 at 03:39
`DROP TRIGGER IF EXISTS t1_AD` – bishop Dec 12 '13 at 00:27

1 Answers1