CODEWITHSUNDEEP
sql
Jared Pochtar
Jared Pochtar

Reputation: 4985

How should I implement a one-to-one relationship in (My)SQL?

I'm relatively new to SQL and I'm not sure how to do a one to one relationship. Having read about the first 3 forms of normalization, I'm currently under the impression that the best way to do this would be to have an intermediary table, ala-3rd from many to many relationships, except each column being declared unique.

Upvotes: 2

Views: 607

Answers (3)

David Baucum
David Baucum

Reputation: 2250

Optional one-to-one relationships do come up in the real world. The easiest way to implement them, IMHO, is with a unique foreign-key.

mysql> describe table_one;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| table_two_id | int(11)      | YES  | UNI | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
0 rows in set (0.00 sec)

mysql> describe table_two;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
+--------------+--------------+------+-----+---------+----------------+
0 rows in set (0.00 sec)

Upvotes: 0

Joe Phillips
Joe Phillips

Reputation: 51100

ImageDetails
------------
id (PK)
name
description


ImageData
------------
id (PK & FK)
blobfield

Something like this might be a good reason/way to do a one-to-one relationship but normally you would just have all your data in one table where each row is what defines the relationship.

One reason for separating them might be to limit access to a specific table for a user while allowing access to other data for that key field.

Another possible reason for having a separate table for a one-to-one relationship is when you do not wish to have null in your column. For instance, if 90% of the data will be null, you may be better off with a completely separate (and smaller) table and joining that field on the PK instead. This is actually considered to be an "optional one-to-one relationship" because you do not truly have one record for every possible relationship.

Upvotes: 6

Sarfraz
Sarfraz

Reputation: 382666

You don't need a third table for one-to-one relationship, but if the relationship is many-to-many then you do need a third intermediary table between them.

Upvotes: 2

Related Questions