Wednesday, 5 February 2014

SQL – One to One (1:1) Table Relationships

None of this post is written by me… but, seeing that I will be using this information in the near future I figured I collect it here for my own reference.

 

The first portion is the best reference I have ever seen to describe one to one relationships in SQL Server. The second portion describes a (1:0/1) relationship with some reasons on why it would exist.

First, The structure of a One to One Relationship:

One-to-one is actually frequently used in super-type/subtype relationship. In the child table, the primary key also serves as the foreign key to the parent table. Here is an example:
alt text

CREATE TABLE Organization
( 
     ID       int PRIMARY KEY
    ,Name     varchar(200)
    ,Address  varchar(200)
    ,Phone    varchar(12)
)
go

CREATE TABLE Customer
( 
     ID              int PRIMARY KEY
    ,AccountManager  varchar(100)
)
go


ALTER TABLE Customer
    ADD  FOREIGN KEY (ID) REFERENCES Organization(ID)
    	ON DELETE CASCADE
    	ON UPDATE CASCADE
go

 

 

And now, The reasoning behind one to one tables

Any relationship requires that the "parent" table (the one side) have a Primary Key (PK), that uniquely identifies each row, and the "child" table (the other side) have a Foreign Key column or columns, that must be populated with values that are the same as some existing value[s] of the Primary Key in the parent table. If you want a one to many (1-M) relationship then the Foreign Key should be an ordinary attribute (column or columns) in the child table that can repeat (there can be many rows with the same value)

 

If you want a one to one (1-1) relationship then the Foreign key should itself be a Primary Key or unique index in the child table that guarantees that there may be at most one row in the child table with that value.

 

A 1-1 relationship effectively partitions the attributes (columns) in a table into two tables. This is called vertical segmentation. A common reason for doing this is if the usage patterns on the columns in the table indicate that a few of the columns need to be accessed significantly more often than the rest of the columns. (Say one or two columns will be accessed 1000s of times per second and the other 40 columns will be accessed only once a month). Partitioning the table in this way in effect will optimize the storage pattern for those two different queries.

 

The above actually creates a 1 to zero or one relationship, which is used for what is called a subtype relationship. This occurs when you have two different entities that share a great number of attributes, but one of the entities has additional attributes that the other does not need. A good example might be Employees, and SalariedEmployees. The Employee table would have all the attributes that all employees share, and the SalariedEmployee table would exist in a (1-0/1) relationship with Employees, with the additional attributes (Salary, AnnualVacation, etc.) that only Salaried employees need.

 

If you really want a 1-1 relationship, then you have to add another mechanism to guarantee that the child table will always have one record for each record/row in the parent table. Generally the only way to do this is by enforcing this in the code used to insert data. This is because if you added referential integrity constraints on two tables that require that rows always be in both, it would not be possible to add a row to either one without violating one of the constraints, and you can't add a row to both tables at the same time.

 

Source:

  1. Damir Sudarevic : http://stackoverflow.com/questions/1722741/defining-a-one-to-one-relationship-in-sql-server
  2. Charles Bretana: http://stackoverflow.com/questions/5112473/designing-11-and-1m-relationships-in-sql-server

Twitter

Page Hits