One to One relationship in MySQL

I'm trying to make a one to one relationship in a MySQL DB. I'm using the InnoDB engine and the basic table looks like this:

CREATE TABLE `foo` (
  `fooID` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` TEXT NOT NULL
)

CREATE TABLE `bar` (
  `barName` VARCHAR(100) NOT NULL,
  `fooID` INT(11) NOT NULL PRIMARY KEY,
  CONSTRAINT `contact` FOREIGN KEY (`fooID`) REFERENCES `foo`(`fooID`)
)

Now once I have set up these I alter the foo table so that the fooID also becomes a foreign key to the fooID in bar. The only issue I am facing with this is that there will be a integrity issue when I try to insert into either. I would like开发者_运维问答 some help, thanks.

A true one-to-one relationship in a relational database is best done by adding a column; If the data in these two tables is always required for every record, then the table should be defined as foo (fooID, name, barname).

You can deffered the checking of constraint to the end of transaction. However I haven't find anything for MySQL (it is not the most advanced database). For PostgreSQL (for other the syntax varies):

ALTER TABLE foo ADD FOREIGN KEY (fooID) REFERENCES bar DEFERRABLE;

Which means that constraint will be checked at the end of transaction.

If you have to use MySQL use Chris Shaffer answer.

I found a "hacky" solution to this, the variable foreign_key_checks can be used.

If one alters the fooID in foo to have a foreign key constraint in bar, one can then insert that as such

SET foreign_key_checks = 0;
INSERT...;
INSERT...;
SET foreign_key_checks = 1;

As I said "hacky" and I would probably not to this on a production system.