CODEWITHSUNDEEP
sqlmysqlforeign-keys
Steve
Steve

Reputation: 3080

Foreign Key doesn't stop me entering data MySQL

I have a table which has one column as a foreign key joining to another table. It's a cricket question where I have a table called Fixtures and another called Inning.

Inning table has a FixtureId column relates to the Fixture table.

I would expect that If i do a insert on the inning table using a FixtureId that doesn't relate to a Fixture then it would error but this isn't the case...

Can anyone explain why this is?

Upvotes: 3

Views: 217

Answers (2)

Daniel Vassallo
Daniel Vassallo

Reputation: 344461

Make sure that you are using the InnoDB storage engine when creating the table. Other storage engines will simply ignore foreign key constraints. (Source)

Example:

CREATE TABLE a (
     id INT AUTO_INCREMENT PRIMARY KEY
) ENGINE=INNODB;

CREATE TABLE b (
     id INT AUTO_INCREMENT PRIMARY KEY,
     a_id INT,
     FOREIGN KEY (a_id) REFERENCES a(id)
) ENGINE=INNODB;

INSERT INTO b (id, a_id) VALUES(NULL, 1);

The above insert fails with:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails...

Upvotes: 6

David Morrow
David Morrow

Reputation: 9354

im not sure i understand can you elaborate and show your sql statements? if i understand correctly, ( which i may not ) your foreign key would just be blank if you did not have a value for that field, why would it error out?

Upvotes: 0

Related Questions