Defining a one-to-one relationship in SQL Server

Question

I need to define a one-to-one relationship, and can't seem to find the proper way of doing it in SQL Server.

Why a one-to-one relationship you ask?

I am using WCF as a DAL (Linq) and I have a table containing a BLOB column. The BLOB hardly ever changes and it would be a waste of bandwidth to transfer it across every time a query is made.

I had a look at this solution, and though it seems like a great idea, I can just see Linq having a little sissy fit when trying to implement this approach.

Any ideas?

Answer 1

Why not make the foreign key of each table unique?

Answer 2

There is no explicit way to define a one-to-one relationship, though you can enforce a one-to-one relationship by creating a standard foreign key and making the corresponding "child" column either the primary key or adding a unique index to it.

For example:

ParentTable
-------------
UserID
FirstName
LastName
...

ChildTable
-------------
UserID
Picture

In this scenario, create a foreign key between ParentTable.UserID and ChildTable.UserID, and define UserID as the primary key in both tables.

Answer 3

there is no such thing as an explicit one-to-one relationship.

But, by the fact that tbl1.id and tbl2.id are primary keys and tbl2.id is a foreign key referenceing tbl1.id, you have created an implicit 1:0..1 relationship.

Answer 4

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:

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

Answer 5

Put 1:1 related items into the same row in the same table. That's where "relation" in "relational database" comes from - related things go into the same row.

If you want to reduce size of data traveling over the wire consider either projecting only the needed columns:

SELECT c1, c2, c3 FROM t1

or create a view that only projects relevant columns and use that view when needed:

CREATE VIEW V1 AS SELECT c1, c2, c3 FROM t1
SELECT * FROM t1
UPDATE v1 SET c1=5 WHERE c2=7

Note that BLOBs are stored off-row in SQL Server so you are not saving much disk IO by vertically-partitioning your data. If these were non-BLOB columns you may benefit form vertical partitioning as you described because you will do less disk IO to scan the base table.