SQL Server Conditional Foreign Key

Question

I have two tables in my SQL Server database, Foo and Bar. Table Foo is like so:

+-------+
|  Foo  |
+-------+
| Id    |
| Type  |
| Value |
+-------+

The table has values like:

+----+--------+-----------+
| Id |  Type  |   Value   |
+----+--------+-----------+
|  1 | Status | New       |
|  2 | Status | Old       |
|  3 | Type   | Car       |
|  4 | State  | Inventory |
|  5 | State  | Sold      |
+----+--------+-----------+

The table Bar is like so:

+----------+
|   Bar    |
+----------+
| Id       |
| TypeId   |
| StatusId |
| StateId  |
+----------+

Where TypeId, StatusId and StateId are all foreign key'ed to the Foo table. But I want to put a condition on each foreign key where they can only key to the Foo ids related to it's type. For example, the TypeId column can ONLY foreign key to id 3 on the Foo table. Or the StatusId column can ONLY foreign key to ids 1 or 2.

I know there is a check function in SQL Server but I'm unsure on how to use it correctly. I tried to do something like this:

CREATE TABLE TEST.dbo.Bar
(
    Id int PRIMARY KEY NOT NULL IDENTITY,
    TypeId int NOT NULL CHECK (Type='Type'),
    CONSTRAINT FK_Bar_Foo_Type FOREIGN KEY (TypeId) REFERENCES Foo (Id, Type)
)
CREATE UNIQUE INDEX Bar_Id_uindex ON TEST.dbo.Bar (Id)

But this didn't work. What am I doing wrong?

Answer 1

The check constraints you are referring to are only used to limit the type of information stored in a key or non key column. So, if you don't want a key column to have a negative value (lets say its a price column, and there is never a negative price) you will use Check constraint.

To better understand the concept of primary and foreign keys:

Primary key uniquely identifies each record in a table. Foreign key is a value in some table which is a unique identifier (and can also be a primary key) in another table. This means that Foreign key can repeat many times in the table in which it is a foreign key in, and it will definitely be unique in the table that it is created from ( in the table that gives meaning to it).

Now coming to your question, you probably need to use the concept of composite keys. A composite key is basically a group of two or more values that uniquely identify a record, because you cannot enforce limitations on foreign keys in the way you are intending to do, because that defeats the very purpose of a key. Handle some issues with type of data stored in your keys at the application layer instead of database layer.

Looking at the problem in this manner will conceptually resolve some design flaws with your tables as as well.