Sql Server Conditional Foreign Key

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: +-

Solution 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.