Suggestions for insert with Foreign key constraint in mysql

Question

I have a table containing 2 entries.

Something like

CREATE TABLE  `db`.`main` (
  `id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
);

The id for these 2 entries are automatically generated primary keys.

I have another table with a rule linking

CREATE TABLE  `db`.`day` (
    `main_id` int(10) unsigned NOT NULL,
    `day` tinyint(4) NOT NULL,
    CONSTRAINT `fk_db_main` FOREIGN KEY (`main_id`) REFERENCES `main` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

now I can successfully get a result using

SELECT * FROM main where id='9';

but when I try to run

INSERT INTO day (main_id, day) VALUES (9, 0);

I get

"Cannot add or update a child row: a foreign key constraint fails (db.day, CONSTRAINT fk_db_main FOREIGN KEY (main_id) REFERENCES main (id) ON DELETE NO ACTION ON UPDATE NO ACTION) (1452)"

Any suggestions on what I am missing with the insert?

**I hadn't listed the actual cause of the issue while asking the question. The actual cause was that the main db table was in MyISAM, and the InnoDB tables couldn't create a foreign key connecting to it. In short, MyISAM doesn't support foreign keys, even when they are coming from other tables.

Answer 1

The FOREIGN KEY constraint says "there shall be an entry in the 'main` table with an ID value that matches the newly inserted 'main_id' value in the 'day' table".

When you INSERT the value 9 into 'day', is there already a row in 'main' with ID = 9?

The DBMS doesn't think so - that's why it complained.

Answer 2

The insert works for me if I remove the db. parts in the CREATE TABLE statements (and insert into main a row with an id of 9). Maybe the problem is that you're using that db. prefix inconsistently, i.e. after TABLE but not in the CONSTRAINT clause...?

Answer 3

I hadn't listed the actual cause of the issue while asking the question. The actual cause was that the main db table was in MyISAM, and the InnoDB tables couldn't create a foreign key connecting to it. In short, MyISAM doesn't support foreign keys, even when they are coming from other tables.