MySQL add primary key after foreign key insert

Question

I have two tables (A, B) with a foreign key from B to A. So A is my parent table and B my child.

I now insert a row in B before the parent row exists in A. So I set the foreign key to an id I know parent A will have, but which is not existing right know. To achieve that I use the option 'SET foreign_key_checks = 0', which allows to set a foreign key in the child B without the existens of the key in the parent A.

My question is, what will happen, if I add the row in A with the missing primary key. Will the foreign key <-> primary key connection work and will it be as fast as normal? Or do I have to drop the fk key and rebuild it?

I use InnoDB and MySQL 5.5.

... and I know that is probably very bad practice...

Or short:

I have a parent and a child table, linked by a foreign key. I add the child first, what happens if I add the parent later?

Answer 1

I tried it myself by creating an example.

CREATE TABLE `parent` (
  `idparent` int(11) NOT NULL,
  PRIMARY KEY (`idparent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `parent` (
  `idparent` int(11) NOT NULL,
  PRIMARY KEY (`idparent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET foreign_key_checks = 0;

INSERT INTO child (idchild, parentid) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);   

SET foreign_key_checks = 1;

INSERT INTO parent (idparent) VALUES (1),(2),(3),(4),(5);

Next, I used explain to get an idea, if the index is used:

EXPLAIN SELECT * from parent p
join child c on c.parentid = p.idparent;

+----+-------------+-------+-------+-----------------+-----------------+---------+----------------------+------+--------------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref                  | rows | Extra                    |
+----+-------------+-------+-------+-----------------+-----------------+---------+----------------------+------+--------------------------+
|  1 | SIMPLE      | p     | index | PRIMARY         | PRIMARY         | 4       | NULL                 |    5 | Using index              |
|  1 | SIMPLE      | c     | ref   | fk_parentid_idx | fk_parentid_idx | 5       | remove_me.p.idparent |    1 | Using where; Using index |
+----+-------------+-------+-------+-----------------+-----------------+---------+----------------------+------+--------------------------+

So it looks like it uses the index, altough at first the foreign key was not set. Therefore, it should be at least speedwise no problem.