mysql foreign key constraints

Question

I have set foreign key integrity in a table. But i am able to delete data from the master table, which is reference in child table.. What would be the problem Actually it should say error on deletion like all referenced table data has to be deleted.

Answer 1

mysql dont do it for you ,

you need declare trigger on delete action

before delete trigger example :

http://www.java2s.com/Code/Oracle/Trigger/Createabeforedeletetrigger.htm

Answer 2

Did you specify ON DELETE CASCADE ? when you created your FK? Don't know which engine you are using either

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 here http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html