How to define one-to-one relationship in MySQL

In Databases, One-to-one relationship occurs when there is exactly one record in the parent table that corresponds to exactly one record in the child table.

For this tutorial I will use the following tools:

• Maria DB 5.5 (can be used any version of MySQL or MariaDB)
• MySQL Workbench (used to edit the tables and relations).

Note: if you need help to setup your development environment, follow the How to prepare a Windows Java development machine tutorial.

MySQL/MariaDB does not contain any special options to define the one-to-one relationship, but you can obtain one-to-one doing this:

• add primary keys in both tables;
• in the second table add a foreign key to point to the primary key from the first table and as constraints add it as unique.

Doing this will force the one-to-one relationship between tables and will force any inserts to the second table to have the foreign key in link with the primary key from the first table.

Example

To exemplify this let's take a classic example of user/profile (user details) tables model:

• user: basic details about a user (Note: ignore the password being in clear)
• user_detail: other details for the user.

Create Script

Here is the create script for the 2 tables to demonstrate the one-to-one database relationship.

-- -----------------------------------------------------
-- Schema db
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `db` DEFAULT CHARACTER SET utf8 ;
USE `db` ;

-- -----------------------------------------------------
-- Table `db`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `db`.`user` ;

CREATE TABLE IF NOT EXISTS `db`.`user` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `db`.`user_detail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `db`.`user_detail` ;

CREATE TABLE IF NOT EXISTS `db`.`user_detail` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `address` VARCHAR(255) NULL DEFAULT NULL,
  `postal_code` VARCHAR(20) NULL DEFAULT NULL,
  `city` VARCHAR(100) NULL DEFAULT NULL,
  `phone_number` VARCHAR(45) NULL DEFAULT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  CONSTRAINT `fk_user_detail_user`
    FOREIGN KEY (`id`)
    REFERENCES `db`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = utf8;

Notes:

• the AUTO_INCREMENT from the second table is not needed because anyway is needed to be specified at insert as part of one-to-one relationship with the first table.
• if you don't want to use in the second table the id column as a foreign key, you can add another column to link the tables. e.g. user_id.

Insert script

If you execute the following script will insert a row in each table, as it follows the one-to-one relationship defined in the tables structure.

INSERT INTO `db`.`user` (`id`, `name`, `email`, `password`)
VALUES('100', 'John Doe', 'john.doe@example.com', 'aaa');


INSERT INTO `db`.`user_detail` (`id`, `address`, `postal_code`, `city`, `phone_number`)
VALUES('100', '91 Wembley Park', 'AB1 2CD', 'London', '123456789');

Now, if you try to run the user_detail insert with a different id, that is not present in the user table you will get an error saying that if failed due to foreign key constraints.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`db`.`user_detail`, CONSTRAINT `fk_user_detail_user` FOREIGN KEY (`id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Query script

Here is a script to query these 2 tables.

SELECT 
	u.id, 
	u.name, 
	u.email, 
	u.password,
	ud.address,
	ud.postal_code,
	ud.city,
	ud.phone_number
FROM `db`.`user` u 
JOIN `db`.`user_detail` ud ON u.id = ud.id;

References

• How to prepare a Windows Java development machine