Ask a Question
Advertise on boostr.in
foreign key not working in mysql why can i insert a value thats not in the for using -'mysql,foreign-keys,innodb,mysql-error-1005'
foreign key not working in mysql why can i insert a value thats not in the for ¬†using -'mysql,foreign-keys,innodb,mysql-error-1005'
I've created a table in MySQL:
CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT,
type ENUM('rate','report','submit','edit','delete') NOT NULL,
Q_id int NOT NULL,
U_id int NOT NULL,
date DATE NOT NULL,
time TIME NOT NULL,
PRIMARY KEY (A_id),
CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id));
This created the table I wanted just fine (although a "DESCRIBE actions;" command showed me that the foreign keys were keys of type MUL, and I'm not sure what this means). ¬†However, when I try to enter a Q_id or a U_id that does not exist in the questions or users tables, MySQL still allows these values.
What did I do wrong? ¬†How can I prevent a table with a foreign key from accepting invalid data?
If I add TYPE=InnoDB to the end, I get an error:
¬†¬†ERROR 1005 (HY000): Can't create table './quotes/actions.frm' (errno: 150)
Why might that happen?
I'm told that it's important to enforce data integrity with functional foreign keys, but also that InnoDB should not be used with MySQL. ¬†What do you recommend?
Sep 7, 2015
to add a comment.
Your name to display (optional):
Email me at this address if my answer is selected or commented on:
Email me if my answer is selected or commented on
Privacy: Your email address will only be used for sending these notifications.
To avoid this verification in future, please