Organizational Research By

Surprising Reserch Topic

Experts Most Trusted Topic


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,
rate tinyint(1),
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?

UPDATE 1

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?

UPDATE 2

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?
    

asked Sep 7, 2015 by rajesh
0 votes
5 views



Related Hot Questions



Government Jobs Opening


...