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.
-
2Which storage engine are you using? "For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it." (MySQL Reference Manual at dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html)Kai Chan– Kai Chan2010-01-24 15:57:52 +00:00Commented Jan 24, 2010 at 15:57
-
The storge engine is ENGINE=MyISAMASD– ASD2010-01-24 16:01:37 +00:00Commented Jan 24, 2010 at 16:01
Add a comment
|
2 Answers
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
4 Comments
Haim Evgi
the Both tables must be InnoDB tables , and the performance of innodb is less than myisam
ASD
Actually i have created table plainly and i have data now. Did you mean that i need to recreate all the table for setting up the foreign key.
Kai Chan
The foreign key constraints are there. MySQL just do not enforce them unless the storage engine is set to InnoDB. You can do an "ALTER TABLE [table name] ENGINE = InnoDB" (dev.mysql.com/doc/refman/5.1/en/alter-table.html) instead of recreating the tables.
ASD
I tried altering the table with innoDB engine. But its not working
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
