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

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.

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.

Thanks for this answer -- saved me a TON of time. By the way, to change the default from MyISAM to InnoDB add "default-storage-engine = innodb" to your my. Cnf file under the mysqld section.

– HDave Jun 18 '10 at 1:16 @HDave: Glad to help! Yes, in recent versions of MySQL there's no reason not to make innodb the default, and in MySQL 5.5 it is. – Bill Karwin Jun 18 '10 at 1:49.

I found the following article. I don't have time to test it out, currently, but it may be helpful: forums.mysql.com/read.php?22,19755,43805 The author,Edwin Dando, says: both tables must be INNODB. The foreign key field must have an index on it.

The foeign key field and the field being referenced must be of the same type (I only use integer) and, after hours of pain, they must be UNSIGNED.

In older versions of MySQL, you had to create an index on the columns manually before declaring a foreign key constraint. In more recent versions, the index is created for you implicitly (if necessary) when you declare the FK. – Bill Karwin Dec 19 '08 at 4:50.

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.

As noted, your table have to be InnoDB for FK constraints to be enforced. I've only run into the 'Can't create table' in the case where I'm trying to create a foreign key constraint where my local column is a different type from the foreign column.

The problem is most likely that questions. P_id and users. P_id are not defined as INT NOT NULL.

For foreign keys to work, the definition of the columns on both side of the foreign key must match exactly, with the exception of auto_increment and default.

I think some of the folks having this problem might be starting out with some of the sample databases provided on the ORACLE website for MYSQL (e.g. Sakila DB). Don't forget to "turn the foreign key constraints back on" at the end of your script (e.g.At the beginning of sakila DB script they are turned OFF): = ) SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; ...create your tables here.... then don't forget this: SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS.

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.

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.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.