How to define one-to-one relationship in MySQL

One-to-one relationships occur when there is exactly one record in the first table that corresponds to exactly one record in the related table.

MySQL does not has any “ready” options to define the one-to-one relationship, if you want to enforce it, you can add a foreign key from one primary key to the other primary key. The primary key have to be unique, doing this, two tables will have the one-to-one relationship automatically.

Example

Here’s an example to define a one-to-one relationship in MySQL.

STOCK table is used to store the frequent use data like stock_code and stock_name, while STOCK_DETAIL is stored the company detail. Both tables have the same Stock_Id as primary key. In STOCK_DETAIL table, Stock_Id is the primary key and also a foreign key to STOCK table.

P.S Another classic one-to-one example is the user and user profile design.

SQL script

This is the SQL statement to create the tables and enforce the relationship.

DROP TABLE IF EXISTS `mkyong`.`stock`;
CREATE TABLE  `mkyong`.`stock` (
  `STOCK_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `STOCK_CODE` varchar(10) NOT NULL,
  `STOCK_NAME` varchar(20) NOT NULL,
  PRIMARY KEY (`STOCK_ID`) USING BTREE,
  UNIQUE KEY `UNI_STOCK_NAME` (`STOCK_NAME`),
  UNIQUE KEY `UNI_STOCK_CODE` (`STOCK_CODE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;
 
DROP TABLE IF EXISTS `mkyong`.`stock_detail`;
CREATE TABLE  `mkyong`.`stock_detail` (
 `STOCK_ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `COMP_NAME` varchar(100) NOT NULL,
 `COMP_DESC` varchar(255) NOT NULL,
 `REMARK` varchar(255) NOT NULL,
 `LISTED_DATE` date NOT NULL,
 PRIMARY KEY (`STOCK_ID`) USING BTREE,
 CONSTRAINT `FK_STOCK_ID` FOREIGN KEY (`STOCK_ID`) REFERENCES `stock` (`STOCK_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;