CODEWITHSUNDEEP
sql-serverlinq
André Haupt
André Haupt

Reputation: 3524

Defining a one-to-one relationship in SQL Server

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 hissy fit when trying to implement this approach.

Any ideas?

Upvotes: 33

Views: 45078

Answers (6)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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:

org_model_00

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

Upvotes: 77

Assaf S.
Assaf S.

Reputation: 31

In my opinion, a better solution for not reading the BLOB with the LINQ query would be to create a view on the table that contains all the column except for the BLOB ones.

You can then create an EF entity based on the view.

Upvotes: 0

JohnnyBizzle
JohnnyBizzle

Reputation: 979

How about this. Link the primary key in the first table to the primary key in the second table.

Tab1.ID (PK) <-> Tab2.ID (PK)

My problem was I have a 2 stage process with mandatory fields in both. The whole process could be classed as one episode (put in the same table) but there is an initial stage and final stage.

Upvotes: 0

DenNukem
DenNukem

Reputation: 8264

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.

Upvotes: 1

Tamil.SQL
Tamil.SQL

Reputation: 262

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.

Upvotes: 2

Myles
Myles

Reputation: 21510

Why not make the foreign key of each table unique?

Upvotes: 3

Related Questions