Correct way to enforce this one-to-one relationship

Question

TableA, TableB and TableC all require a single row from TableX but a row of TableX cannot be shared between more than one row from tables A-C. (Tables A-C are completely different).

I can think of a few solutions for this, but which one (if any) is correct?

Option 1

Flatten the fields on TableX onto Tables A-C (my least preferable option).

Option 2

This essentially seems like a many-to-one link, but easiest to enforce in the database as a one-to-one. Doesn't work so well with entity framework.

Tables A-C

[Id] IDENTITY INT NOT NULL,
[TableAId] INT NULL,
[TableBId] INT NULL,
[TableCId] INT NULL,

CONSTRAINT [PK_TableX] PRIMARY KEY,
CONSTRAINT [FK_TableX_TableA] FOREIGN KEY [TableAId] REFERENCES [TableA]([Id]),
CONSTRAINT [FK_TableX_TableB] FOREIGN KEY [TableBId] REFERENCES [TableB]([Id]),
CONSTRAINT [FK_TableX_TableC] FOREIGN KEY [TableCId] REFERENCES [TableC]([Id]),    
CONSTRAINT [UQ_TableX_TableAId] UNIQUE ([TableAId]),
CONSTRAINT [UQ_TableX_TableBId] UNIQUE ([TableBId]),
CONSTRAINT [UQ_TableX_TableCId] UNIQUE ([TableCId]),
CONSTRAINT [CK_TableX_Owner] CHECK
(
    ([TableAId] IS NOT NULL AND [TableBId] IS NULL AND [TableCId] IS NULL) OR
    ([TableBId] IS NOT NULL AND [TableCId] IS NULL AND [TableAId] IS NULL) OR
    ([TableCId] IS NOT NULL AND [TableAId] IS NULL AND [TableBId] IS NULL)
)

TableX

[Id] IDENTITY INT NOT NULL

Option 3

This is my favoured option as it seems to represent the one-to-one link the best (as we can't have the Id of TableX match the primary keys of tables A-C. The ORM (in this case Entity Framework) would handle the rest.

Tables A-C

[Id] IDENTITY INT NOT NULL,
[TableXId] INT NOT NULL,

CONSTRAINT [PK_Table*] PRIMARY KEY,
CONSTRAINT [FK_Table*_TableX] FOREIGN KEY [TableXId] REFERENCES [TableX]([Id]) ON DELETE CASCADE
CONSTRAINT [UQ_Table*_TableXId] UNIQUE ([TableXId])

TableX

Id IDENTITY INT NOT NULL,

CONSTRAINT [PK_TableX] PRIMARY KEY

http://stackoverflow.com/questions/11047391/foreign-key-refering-to-multiple-tables/11050229#11050229 http://stackoverflow.com/questions/1654071/db-design-to-use-sub-type-or-not/1654483#1654483 — Damir Sudarevic, Dec 29 '13 at 10:13
If all three tables reference a single one, they're not supposed to share a row, but you're worried that they _will_, then they have some sort of relationship. What is it you're doing that this is an issue; what types of layouts/entities? Option 3 only makes `x.id` unique **per table** - there can be a matching value in each table. Was that what you wanted, or was it unique in _all three_? — Clockwork-Muse, Dec 29 '13 at 10:15
Unique in all three. Basically `TableX` is some settings which can be applied to Tables A-C (and possibly more in the future), hence why I didn't want to flatten them :) — Jamie, Dec 29 '13 at 10:18

score 0 · Answer 1 · answered Dec 29 '13 at 10:45

Option 4:

dbo.TableX

Id IDENTITY INT NOT NULL,
CONSTRAINT [PK_TableX] PRIMARY KEY,
Type CHAR(1) NOT NULL,
CONSTRAINT CK_TableX_VerifyType CHECK( Type IN ('A', 'B', 'C') ),
CONSTRAINT UQ_TableX_Id_Type UNIQUE (Id, Type)

dbo.Table{A|B|C}

[TableXId] INT NOT NULL,
CONSTRAINT [PK_Table*] PRIMARY KEY (TableXId),
CONSTRAINT [FK_Table*_TableX_TableXId] FOREIGN KEY ([TableXId]) REFERENCES [TableX]([Id]) ON DELETE CASCADE,
Type CHAR(1) NOT NULL,
CONSTRAINT CK_Table*_VerifyType CHECK( Type = 'A' ) -- For dbo.TableA or ='B' for dbo.TableB or ...
CONSTRAINT [FK_Table*_TableX_TableXId_Type] FOREIGN KEY ([TableXId], Type) REFERENCES [TableX]([Id], Type) ON DELETE CASCADE,

score 0 · Answer 2 · edited May 23 '17 at 12:11

That's not strictly a "one to one" relationship. That's actually a set of 3 "one to zero or one" relationships with the additional condition of "exclusivity" (tables A, B and C cannot share a row in X) and "presence" (a row in X cannot be without a row in A, B or C).

On a DBMS that supports deferred foreign keys, both exclusivity and presence can be enforced declaratively.
On a DBMS that doesn't¹, only exclusivity can be enforced declaratively, and presence must be enforced from the application code.

For the description how to actually do that, please see: Supertype-subtype database design.

¹ MS SQL Server unfortunately falls into that category.

score 0 · Answer 3 · answered Dec 29 '13 at 16:03

0

You could create an indexed view over A, B, and C

create view myView as 

   select xId from tableA
   union all
   select xId from tableB
   union all
   select xId from tableC

go

create unique clustered index [CIX_myView] on dbo.myView (xId)

answered Dec 29 '13 at 16:03

Ben Thul

31,080
4
45
68

Correct way to enforce this one-to-one relationship

Option 1

Option 2

Tables A-C

TableX

Option 3

Tables A-C

TableX

3 Answers3