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