Foreign key not working in MySQL: Why can I INSERT a value that's not in the foreign column?

Question

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?

Solution 1

I would guess that your default storage engine is MyISAM, which ignores foreign key constraints. It silently accepts the declaration of a foreign key, but does not store the constraint or enforce it subsequently.

However, it does implicitly create an index on the columns you declared for the foreign key. In MySQL, "KEY" is a synonym for "INDEX". That's what's being shown in the DESCRIBE output: an index, but not a constraint.

You are able to insert invalid values to the table right now because there is no constraint. To get a constraint that enforces referential integrity, you must use the InnoDB storage engine:

CREATE TABLE actions (
  A_id int NOT NULL AUTO_INCREMENT,
  ...
  CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id),
  CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id)
) ENGINE=InnoDB;

I've always thought it was a big mistake on MySQL's part to silently ignore foreign key constraint declarations. There's no error or warning that the storage engine doesn't support them.

The same is true for CHECK constraints. By the way no storage engine used with MySQL supports CHECK constraints but the SQL parser accepts them with no complaint.

---

The errno 150 issue occurs when it cannot create the InnoDB table, because it couldn't make sense of the foreign key constraint. You can get some more information with:

SHOW ENGINE INNODB STATUS;

Some requirements for InnoDB foreign keys:

• Referenced table must also be InnoDB.
• Referenced table must have an index and a primary key.
• SQL data types of FK column and referenced PK column must be identical. For example, INT does not match BIGINT or INT UNSIGNED.

---

You can change the storage engine of a table that has data in it:

ALTER TABLE actions ENGINE=InnoDB;

This effectively copies the entire MyISAM table to an InnoDB table, then once that succeeds it drops the MyISAM table and renames the new InnoDB table to the name of the former MyISAM table. This is called a "table restructure" and it can be time-consuming, depending on how much data is in the table. A table restructure occurs during ALTER TABLE, even in some cases where it may seem unnecessary.

---

Re your 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?

Who told you that? It's absolutely false. InnoDB has better performance than MyISAM (though InnoDB needs more attention to tuning the configuration), InnoDB supports atomic changes, transactions, foreign keys, and InnoDB is much more resistant to corrupting data in a crash.

Unless you're running an old, unsupported version of MySQL (5.0 or earlier) you should use InnoDB as your default storage engine choice, and use MyISAM only if you can demonstrate a specific workload that benefits from MyISAM.

Solution 2

Just to save other's of the hours of headache I've been thru - as giraffa touches upon, ensure @FOREIGN_KEY_CHECKS is set to 1.

SELECT @@FOREIGN_KEY_CHECKS

SET FOREIGN_KEY_CHECKS=1

Solution 3

I know this thread was opened long time ago, but I am posting this message for future users who will look for the answer. I was having the same problem with foreign key in mysql. The following thing worked for me.

Parent table:

CREATE TABLE NameSubject (
  Autonumber INT NOT NULL AUTO_INCREMENT,
  NameorSubject nvarchar(255),
  PRIMARY KEY (Autonumber)
 ) ENGINE=InnoDB;

Child Table:

CREATE TABLE Volumes (
  Autonumber INT NOT NULL,
  Volume INT,
  Pages nvarchar(50),
  Reel int,
  Illustrations bit,
  SSMA_TimeStamp timestamp,
  Foreign KEY (Autonumber) references NameSubject(Autonumber)
  ON  update cascade 
)engine=innodb;

"ON update cascade" did the magic for me.

I hope this works for others. Best of luck.

Solution 4

For those who still have problems with mysql ignoring foreign keys constraints and for those who the answers above or in any other related question didn't solve teir puzzle, here is what I found to be the issue.

If you declare your foreign keys as such

id  INTEGER UNSIGNED    REFERENCES    A_Table(id)

Then the foreign key seems to be ignored, to enforce the constraint without (apparently) having to use any of the SET commands, use the following declaration.

id  INTEGER UNSIGNED,
CONSTRAINT fk_id  FOREIGN KEY (id)  REFERENCES A_Table(id)

This way solved the problem for me. Not sure why, as many say the first declaration is only a shorthand to the second variant.

Solution 5

This answer would have saved me a lot of time if I'd seen it first. Try the following three steps, which I've ordered by frequency of newbie mistakes:

(1) Change the table to be InnodDB by appending "ENGINE=InnoDB" to your "CREATE TABLE" statements.

Other engines, which may be the default, do not support foreign key constraints, but neither do they throw an error or warning telling you they're not supported.

(2) Make sure foreign key constraints are in fact being checked by executing "SET foreign_key_checks = 'ON'"

(3) Append "ON UPDATE CASCADE" to your foreign key declaration.

Note: Make sure that cascading is the behavior you want. There are other options...

View more solutions