Click here to Skip to main content
12,819,402 members (31,664 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


Posted 3 Nov 2011

SQL one to one relations

, 6 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
How to define a one to one relation

Yesterday, I was Googling for ‘sql create "one to one" relationship’ and found lots of answers and discussions (try it

Most discussions where about why a one-to-one relation is useless, or why it needed to be used, the others showed complex diagrams, SQL code samples and other methodologies. But none of them gave the simple answer (in my opinion) to the question.

In the hope that next time when someone Googles for ‘sql create "one to one" relationship’ he will find the simple answer, here it is:

A one to one relation is defined by having two tables sharing a single key stored in a column, this column(key) must be indexed and marked as unique. The keys are then used to form a relation.

There, that was it, easy? Or (in)correct? Let me know :)

Frans de Wit

P.S. (to answer the 'why' question)

All sorts of reasons can be found by Googling for the subject. And I am not going to list them here. For me 'there can be only one' (and most, if not all, of the found reasons can be translated back to this), inheritance!

One to one relations are inheritance relations.

The base class supplies a foreign key (this may/can/should be its primary key (it has an index marked unique)), the derived class can use it as its primary key (it is also indexed and marked unique) but can also store it in some other column (that needs to be indexed and marked as unique).

When the derived class stores the inherited key (the primary key of the base class) in another field/column (other than the/its primary key), then this could be repeated for multiple relations and this would be multiple inheritance.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Netherlands Netherlands
No Biography provided

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170308.1 | Last Updated 6 Nov 2011
Article Copyright 2011 by FDW
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid