在 SQL Server 中定义一对一关系

我需要定义一对一的关系，但在 SQL Server 中似乎找不到正确的方法.

你问为什么是一对一的关系?

我使用 WCF 作为 DAL (Linq) 并且我有一个包含 BLOB 列的表.BLOB 几乎不会改变，每次进行查询时都将其传输过来会浪费带宽.

我查看了

创建表组织(ID int PRIMARY KEY,名称 varchar(200),地址 varchar(200),电话 varchar(12))走创建表客户(ID int PRIMARY KEY,AccountManager varchar(100))走ALTER TABLE 客户添加外键 (ID) 引用 组织 (ID)删除级联更新级联走

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?

解决方案

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