CODEWITHSUNDEEP
sqlsql-servert-sqlforeign-keyscheck-constraints
Ross
Ross

Reputation: 11

Complex foreign key

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.

Upvotes: 1

Views: 2269

Answers (2)

SQB
SQB

Reputation: 4078

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.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 2

Related Questions