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

Yes you can link a FK to composite unique. But you have to link to the whole key. And the types need to be the same. And why is Wall even in the question? — paparazzo
– paparazzo, Commented May 8, 2013 at 19:24
Does this mean i have to add in the proxy and the wall table additional columns? This means that the howl column will have the same value. — Jan Van Looveren
– Jan Van Looveren, Commented May 8, 2013 at 19:35

ypercubeᵀᴹ · Accepted Answer · 2013-05-08 20:06:34Z

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

Collectives™ on Stack Overflow

Foreign key or link between part of column selection

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related