Deleting rows from other tables if row is deleted from main table

Question

I have a table called 'pages'. Each page has a content type of either text, video or image. Depending on what the content type is a record is also inserted into either text, video or image table.

When I delete a row from 'pages' how can I ensure the rows from the other table is removed? I understand a little about CASCADE but I am not using foreign keys. At least not explicitly.

My tables look similar to below (I've stripped out fields that are no relevant to the question)

Pages: id, title, content

Video: pid (page id), youtube_url

Any help would be much appreciated.

Answer 1

if your table is innodb engine you can declare foreign key + on delete cascade,

example :

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

more on : http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

another way is declare trigger - before delete .

the manual :

http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

a little example you can find :

http://stackoverflow.com/questions/1948047/trigger-before-delete-mysql