Enforcing referential integrity on among distantly related tables

Question

I have a four tables, which look a bit like this:

CREATE TABLE [TableA]
    ( [ID]    INT     NOT NULL IDENTITY (1, 1) PRIMARY KEY )
CREATE TABLE [TableB]
    ( [A_ID]  INT     NOT NULL FOREIGN KEY REFERENCES [TableA] ([ID]) 
    , [Value] TINYINT NOT NULL 
    , PRIMARY KEY ( [A_ID], [Value] ) )
CREATE TABLE [TableC]
    ( [ID]    INT     NOT NULL IDENTITY (1, 1) PRIMARY KEY
    , [A_ID]  INT     NOT NULL FOREIGN KEY REFERENCES [TableA] ([ID]) )
CREATE TABLE [TableD]
    ( [ID]    INT     NOT NULL IDENTITY (1, 1) PRIMARY KEY
    , [C_ID]  INT     NOT NULL FOREIGN KEY REFERENCES [TableC] ([ID])
    , [Value] TINYINT NOT NULL )

But I'd like to enforce referential integrity on TableD such that a valid value may only be one of the values from TableB of the TableA associated with the parent TableC record. For example:

TableA: ID 
         1
TableB: A_ID | VALUE
           1 |     1
           1 |     2
TableC: ID | A_ID
         7 |    1
TableD: ID | C_ID | VALUE
         1 |    7 |     1 -- Fine, Inserted
         1 |    7 |     2 -- Fine, Inserted
         1 |    7 |     3 -- Invalid, Rejected!

Now, I tried adding an A_ID column to TableD like this:

CREATE TABLE [TableD]
    ( [ID]    INT     NOT NULL IDENTITY (1, 1) PRIMARY KEY
    , [C_ID]  INT     NOT NULL 
    , [A_ID]  INT     NOT NULL 
    , [Value] TINYINT NOT NULL
    , FOREIGN KEY ([C_ID], [A_ID]) REFERENCES [TableC] ([ID], [A_ID])
    , FOREIGN KEY ([A_ID], [Value]) REFERENCES [TableB] ([A_ID], [Value]) )

But I get the following error:

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'TableC' that match the referencing column list in the foreign key 'FK__TableD__TableC'.

Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

I don't really like this solution anyway because it seems denormalized—since I have the TableC→TableA relationship stored in two different places—but I can't think of any other way to enforce referential integrity (other than triggers, which I'd like to avoid).

Is there any way to achieve this?

that's not what foreign keys are for. they're just there to check if a parent record exists. Not to enforce a particular ancestry count. You want a `check` constraint instead. — Marc B, Aug 12 '13 at 16:11
http://technet.microsoft.com/en-us/library/ms188258%28v=sql.105%29.aspx — Marc B, Aug 12 '13 at 16:17
@MarcB I saw that, but I'm not sure how to apply it in my case. It can't just be a simple function call, it would have to be something like `CHECK ([Value] IN (SELECT [Value] FROM [TableB] ... ))` — p.s.w.g, Aug 12 '13 at 16:58
those functions can be stored procedures. the only restriction is that the function you use (or create) returns true/false. — Marc B, Aug 12 '13 at 18:28

score 1 · Accepted Answer · answered Aug 12 '13 at 19:53

1

Yes, there is a way. You can add unique constraint to table TableC:

CREATE TABLE [TableC]
    ( [ID]    INT     NOT NULL IDENTITY (1, 1) PRIMARY KEY
    , [A_ID]  INT     NOT NULL FOREIGN KEY REFERENCES [TableA] ([ID]),
     CONSTRAINT UK_TableC UNIQUE ([ID], [A_ID]) )

and then you will be able referencing TableC in table TableD as you specified:

CREATE TABLE [TableD]
    ( [ID]    INT     NOT NULL IDENTITY (1, 1) PRIMARY KEY
    , [C_ID]  INT     NOT NULL 
    , [A_ID]  INT     NOT NULL 
    , [Value] TINYINT NOT NULL
    , FOREIGN KEY ([C_ID], [A_ID]) REFERENCES [TableC] ([ID], [A_ID])
    , FOREIGN KEY ([A_ID], [Value]) REFERENCES [TableB] ([A_ID], [Value]) )

answered Aug 12 '13 at 19:53

i-one

5,050
1
28
40

Thanks for your response, I'll try this out (as well as Marc B's suggestion) as soon as I can. – p.s.w.g Aug 12 '13 at 20:27
This works. It's strange that I have to add a unique constraint. Since `ID` is the primary key any pair of columns including `ID` is guaranteed to be unique, but I suppose that's SQL. I've also managed to get Mark B's suggestion working as well, but I'm not sure which one I prefer. This seems more reliable since breaking changes in `TableB` will automatically be rejected thanks to the FK on `TableD`, but the check condition appears to preserve normalization. Perhaps that's a question for another day. – p.s.w.g Aug 14 '13 at 18:44

Enforcing referential integrity on among distantly related tables

1 Answers1