Complex foreign key

Question

I have a table similar to this:

    CREATE TABLE [dbo].[Table1](
[Option_PK] [bigint] IDENTITY(1,1) NOT NULL,
[Option_1] [varchar](10) NULL,
[Option_2] [varchar](10) NULL,
[Option_3] [varchar](10) NULL)

What I am attempting to do, is add a table driven constraint which can effectively restrict valid entries on a per-column basis. For example, if I made a second table:

    CREATE TABLE [dbo].[Table2](
    [FK_Name] [varchar](10) NOT NULL,
    [FK_Value] [varchar](10) NOT NULL)

I would then want to check that the value stored in Table1, column "Option_1", existed in Table2, column "FK_Value", where the value of "FK_Name" was "Option_1".

Is this possible with either a check or an FK?

** Edit to make the column datatypes match; I hand typed the example table declarations and typo'd, this wasn't relevant to the problem. I know how to do a FK, I don't know how to do an FK like what I'm describing.

Possible dublicate: http://stackoverflow.com/questions/9612379/creating-composite-foreign-key-constraint — faester, Jan 21 '13 at 20:48
Why would you want to do this? You should link table2 to a code table with the real values you want to verify — Brian White, Jan 21 '13 at 21:05
@BrianWhite - I am not sure I understand. The objective here is that if the column "Option1" in Table1 gets a row with a value of "T", that I have a table-driven list of appropriate values for "Option1" (a foreign key) which checks if "T" is acceptable. I could do this easily with many FK tables (1 table per FK), but there are a LOT of columns to validate; I would prefer a simpler solution than ~20 FK tables to maintain. — Ross, Jan 22 '13 at 15:35
"Table1" is a list of customer configurable settings. Several different apps will write to "Table1". If I have to have 20 different FK tables I will, but it seems like there ought to be a better way. As it stands, if the list of allowable values changes, all 20 tables would need to be reviewed, rather than one or two tables. — Ross, Jan 23 '13 at 15:14
I also realize I am trying to shift some app-level work to the database, but, for various task-specific reasons, that's what I've been asked to do. — Ross, Jan 23 '13 at 15:15
See this article "Antipathy for Entity Attribute Value data models" in sqlservercentral: http://www.sqlservercentral.com/articles/data+model/95918/. You are going down a common path of recreating basic functionality of an RDBMS. If it's for custom text that's small enough that it can work. As for enforcing an FK like effect - just don't. Let the customer type in "10" in two text fields identically to link the fields and tell them they need to maintain that correctly. Show them in the UI when it's not linked - that should be enough — Brian White, Jan 23 '13 at 18:00
How is update with proper FK more complex? insert into tableFakeFK ([FK_Name], [FK_Value]) values ('option1', 'option1valueA') versus insert into tableFK1 ([FKvalue]) values ('option1valueA') — paparazzo, Jan 24 '13 at 20:34

paparazzo · Answer 1 · 2013-01-21T22:13:41.670

Could you not just have 3 tables and three FK?
A FK needs to match types.

CREATE TABLE [dbo].[Option1]([FK_Value] [nchar](10) NOT NULL)
CONSTRAINT [PK_Option1] PRIMARY KEY CLUSTERED ([FK_Value] ASC)

ALTER TABLE [dbo].[Table1]  WITH CHECK ADD  CONSTRAINT [FK_Table1_Option1] FOREIGN KEY([Option_1])
REFERENCES [dbo].[Table2] ([FK_Value])
GO

Or you could have a column Option1 that defaults to a value of option1
I tried hard coding in a value for Option1 but would not go.

ALTER TABLE [dbo].[FKtest1]  WITH CHECK ADD  CONSTRAINT [FK_FKtest1_FKtest1] FOREIGN KEY([Option1],[ValueFK])
REFERENCES [dbo].[FKtest1FK] ([PKoption],[PKvalue])
GO

You've missed the fact that it should only validate against rows in `[dbo].[Table2]` where `FK_Name = Option_1` — Martin Smith, Jan 21 '13 at 21:38

SQB · Answer 2 · 2013-11-01T13:19:54.550

CREATE TABLE [dbo].[Table1](
   [Option_PK] [bigint] IDENTITY(1,1) NOT NULL,
   [Option_1] [varchar](10) NULL,
   [Option_1_FK] [varchar](8) NOT NULL DEFAULT 'OPTION_1',
   [Option_2] [varchar](10) NULL,
   [Option_2_FK] [varchar](8) NOT NULL DEFAULT 'OPTION_2',
   [Option_3] [varchar](10) NULL,
   [Option_3_FK] [varchar](8) NOT NULL DEFAULT 'OPTION_3'
)

CREATE TABLE [dbo].[Options](
   [FK_Name] [nchar](8) NOT NULL,
   [FK_Value] [nchar](10) NOT NULL,
   CONSTRAINT [PK_Option1] PRIMARY KEY CLUSTERED ([FK_Name], [FK_Value] ASC)
)

ALTER TABLE [dbo].[Table1] WITH CHECK ADD 
   CONSTRAINT [FK_Table1_Option1] FOREIGN KEY([Option_1], [Option_1_FK) REFERENCES [dbo].[Options] ([[FK_Value], [FK_Name])
   CONSTRAINT [FK_Table1_Option2] FOREIGN KEY([Option_2], [Option_2_FK) REFERENCES [dbo].[Options] ([[FK_Value], [FK_Name])
   CONSTRAINT [FK_Table1_Option3] FOREIGN KEY([Option_3], [Option_3_FK) REFERENCES [dbo].[Options] ([[FK_Value], [FK_Name])
GO

Which is untested, unnormalized and ugly. You should probably add constraints to ensure the value of Option_X_FK does not change. Actually, this being T-SQL, you might be able to use computed columns for that, but I'm not sure if including them in a foreign key is allowed.

Complex foreign key

2 Answers2