Foreign key or link between part of column selection

Question

I have 3 tables in the following setup

CREATE TABLE [dbo].[codevariable] (
    [id] [int] NULL,
    [code] [nchar](10) NULL,
    [variable] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[proxy] (
    [id] [int] NULL,
    [description] [nvarchar](50) NULL,
    [status] [bit] NULL,
    [added] [datetime] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[wall] (
    [id] [int] NULL,
    [description] [nvarchar](50) NULL
) ON [PRIMARY]

Following values in the tables Table Wall

1   This is a basic wall
2   This is a medium wall
3   This is an advanced wall

Table Proxy

1   Small Proxy True    2013-05-08 00:00:00.000
2   Medium Proxy    False   2013-05-08 00:00:00.000

Table CodeVariable

1   Proxy       1         
2   Proxy       2         
3   Wall        1         
4   Wall        2         
5   Wall        3      

Owke now the issue that i am facing, if i want to insert lets say a new line in proxy. Then it will have Id 3, now i need to make sure that id 3 also exists in CodeVariable under the code Proxy!

Without foreign key there is no check if the code exists in code variable.

I have tried with foreign keys but without success. How can i create a link between the CodeVariable table on the columns code and variable towards table proxy and table wall.

I can also created index on Code and variable that is unique. but u cannot link a foreign key to it.

I am using SQL 2008

Thanks

Answer 1

One way to change the table definitions, so FOREIGN KEY constraints can be enforced.

Use a composite primary key (codeid, codetype) for table codevariable (renamed code), where codetype can take only 2 possible values, either 'P' or 'W'.
(The code you had could be used instead of the codetype but I preferred a narrower column as keys (primary and foreign) are used in indexes.
The code was converted to a computed column):

CREATE TABLE [dbo].[code] (
    [codeid] [int] NOT NULL,
    [codetype] [char](1) NOT NULL,
    [codename] AS                               -- computed column
         CASE codetype WHEN 'P' THEN 'Proxy'
                       WHEN 'W' THEN 'Wall'
         END,
    CONSTRAINT code_PK
        PRIMARY KEY (codeid, codetype),
    CONSTRAINT codetype_CK
        CHECK (codetype IN ('P', 'W'))
) ;

In the two other tables, the codetype was added as well, so the (codeid, codetype) can be defined as both PRIMARY and FOREIGN key:

CREATE TABLE [dbo].[proxy] (
    [proxyid] [int] NOT NULL,
    [codetype] [char](1) NOT NULL DEFAULT 'P',
    [description] [nvarchar](50) NULL,
    [status] [bit] NULL,
    [added] [datetime] NULL,
    CONSTRAINT proxy_PK
        PRIMARY KEY (proxyid, codetype),
    CONSTRAINT code_proxy_FK
        FOREIGN KEY (proxyid, codetype)
        REFERENCES code (codeid, codetype),
    CONSTRAINT codetype_proxy_CK
        CHECK (codetype = 'P')
) ;


CREATE TABLE [dbo].[wall] (
    [wallid] [int] NOT NULL,
    [codetype] [char](1) NOT NULL DEFAULT 'W',
    [description] [nvarchar](50) NULL,
    CONSTRAINT wall_PK
        PRIMARY KEY (wallid, codetype),
    CONSTRAINT code_wall_FK
        FOREIGN KEY (wallid, codetype)
        REFERENCES code (codeid, codetype),
    CONSTRAINT codetype_wall_CK
        CHECK (codetype = 'W')
) ;

SQL-Fiddle