One to One relationship in MySQL

Question

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.

Answer 1

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).

Answer 2

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.

Answer 3

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.