Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 941
André Haupt Avatar asked Nov 12 '09 14:11

André Haupt


People also ask

How do you write a one-to-one relationship in SQL?

One way to implement a one-to-one relationship in a database is to use the same primary key in both tables. Rows with the same value in the primary key are related. In this example, France is a country with the id 1 and its capital city is in the table capital under id 1.

How do I create a one-to-many relationship in SQL Server?

How to implement one-to-many relationships when designing a database: Create two tables (table 1 and table 2) with their own primary keys. Add a foreign key on a column in table 1 based on the primary key of table 2. This will mean that table 1 can have one or more records related to a single record in table 2.

What is an example of a one-to-one relationship?

Here are some examples of one-to-one relationships in the home: One family lives in one house, and the house contains one family. One person has one passport, and the passport can only be used by one person. One person has one ID number, and the ID number is unique to one person.

How do you define a relationship in SQL?

Relationships are the established associations between two or more tables. Relationships are based on common fields from more than one table, often involving primary and foreign keys. A primary key is the field (or fields) that is used to uniquely identify each record in a table.


4 Answers

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
like image 189
Damir Sudarevic Avatar answered Oct 15 '22 20:10

Damir Sudarevic


Why not make the foreign key of each table unique?

like image 42
Myles Avatar answered Oct 15 '22 22:10

Myles


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.

like image 29
Tamil.SQL Avatar answered Oct 15 '22 20:10

Tamil.SQL


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.

like image 38
DenNukem Avatar answered Oct 15 '22 20:10

DenNukem