在SQL Server中定义一对一关系 [英] Defining a one-to-one relationship in SQL Server

问题描述

我需要定义一对一的关系，似乎无法在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?

我将WCF用作DAL(Linq)，并且我有一个包含BLOB列的表. BLOB几乎不会更改，并且每次查询时都无法在BLOB上进行传输.

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.

我查看了此解决方案，尽管这似乎是个好主意，但在尝试实施此方法时，我只能看到Linq有点不满.

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.

有什么想法吗?

推荐答案

实际上在超类型/子类型关系中经常使用一对一的关系.在子表中，主键还用作父表的外键.这是一个示例:

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

这篇关于在SQL Server中定义一对一关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！