33

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?

André Haupt
  • 3,294
  • 5
  • 32
  • 57

6 Answers6

76

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
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • 5
    Thank god someone here knows SQL and Relational databases. You have shown that the relationship is **explicit**. Just to be clear, for those who are not sure the relationship is 1::1, note that the Customer.ID, which is also the Organisation.ID, is **unique** and therefore there can be only one Customer row for any Organisation row. – PerformanceDBA Mar 06 '11 at 07:19
  • 30
    @PerformanceDBA: the relationship is `1:0..1` because it lacks a constraint to ensure that for each row in the parent table there is a matching row in one of the subtype tables. For the schema as posted, for each row in `Organization` there can be either zero or one row in Customer, hence `1:0..1`. – onedaywhen Mar 10 '11 at 14:36
  • 1
    @oneday. Thanks, but your explanation of the surface issue (obvious) is not relevant to the deeper issue. The basis of your comment, and not the comment itself, is incorrect. You have the same misunderstanding as Adam (who has since deleted his answer). The issue cannot be dealt with in comments. If you are genuinely interested in understanding Relational Database nomenclature, please ask a new question, and I will answer it fully. I will not answer further comments. – PerformanceDBA Mar 11 '11 at 08:59
  • @PerformanceDBA: I deleted my answer because the discussion devolved. As requested, I've posted a question so that you can answer properly: http://stackoverflow.com/questions/5279984/defining-a-11-relationship-within-a-relational-database – Adam Robinson Mar 12 '11 at 00:48
3

Why not make the foreign key of each table unique?

Myles
  • 20,860
  • 4
  • 28
  • 37
  • So if I have key in tableA = 3 and key in tableB = 4, they are unique within their table but there is no relationship. – JeffO Nov 12 '09 at 17:08
  • 1
    However, if you tableA.id = 3 and tableB.tableAId = 3 and tableB.tableAId is unique, and you do the same for tableB to tableA, then you are guaranteed to have at most a one to one. – Myles Nov 12 '09 at 17:43
2

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.

Tamil.SQL
  • 262
  • 2
  • 9
1

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.

DenNukem
  • 8,014
  • 3
  • 40
  • 45
0

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.

Joshua Dwire
  • 5,415
  • 5
  • 29
  • 50
Assaf S.
  • 31
  • It's not necessary to sign your posts, SO does that for you automatically. Also, answers should generally show examples of code that does what you are suggesting. :| – vdbuilder Nov 06 '12 at 17:02
0

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.

JohnnyBizzle
  • 971
  • 3
  • 17
  • 31