Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

SQL one to one relations

, 6 Nov 2011
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 http://www.google.com/search?q=sql+create+one+to+one+relationship).

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 Smile | :)

Regards,
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.

License

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

Share

About the Author

FDW

Netherlands Netherlands
No Biography provided

Comments and Discussions

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