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 contains any “ready” options to define the one-to-one relationship, but, if you want to enforce it, you can add a foreign key from one primary key to the other primary key, by doing this, both tables will have the one-to-one relationship automatically.

Example

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

one-to-one-relationship

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 contains the same Stock_Id as primary key. And, in STOCK_DETAIL table, Stock_Id is the primary key and also a foreign key to STOCK table.

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

MySQL Script

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


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;

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Binghong
Guest
Binghong

This is not one-to-one but one-to-one or zero. You can have a stand alone stock without stock detail.

Rani Abidli
Guest
Rani Abidli

thank you

Zeeshan Tufail
Guest
Zeeshan Tufail

What if stock details has an other one-to-one relationship with another table than stock?

Majbah Habib
Guest
Majbah Habib

excellent example. Thanks

Johnny Phan
Guest
Johnny Phan

Thanks, you remind me of the correct way to define one-to-one relationship: no need auto-increment ID on the second table. It felt somehow redundant!

Ammar Bozorgvar
Guest
Ammar Bozorgvar

thank you

James Poulson
Guest
James Poulson

Nice and concise explanation. What if there are multiple child tables ?

Mohammad Najar
Guest
Mohammad Najar

Excellent tutorial.. you rock man.

trackback
Hibernate – One-to-one relationship example | Java Bulgaria

[…] This is a one-to-one relationship table design, a STOCK table contains exactly one record in STOCK_DETAIL table. 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. This is one of the common ways to define one-to-one relationship. More detail… […]

trackback
Hibernate – One-to-one relationship example | Hibernate

[…] This is a one-to-one relationship table design, a STOCK table contains exactly one record in STOCK_DETAIL table. 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. This is one of the common ways to define one-to-one relationship. More detail… […]