0

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?

Richard
  • 5,840
  • 36
  • 123
  • 208
  • So not all ID's should be able to have a type, status, and state? That seems odd to me, or am i missing something – S3S Aug 17 '18 at 19:03
  • 4
    CHECK is used to ensure the value in a column is within certain hardcoded values. This is NOT what you want here at all. What you need is a constraint that uses a function. This has been asked and answered dozens of times. – Sean Lange Aug 17 '18 at 19:03
  • 1
    FWIW "foreign key" is not a verb, it is a noun. :) – Sean Lange Aug 17 '18 at 19:04
  • 1
    I don't think foreign keys work as you want. This is somewhat related to "polymorphic foreign keys" but it's not quite the same. – The Impaler Aug 17 '18 at 19:14
  • I think you'll be better off using triggers to enforce these FKs on insert/update/delete. – The Impaler Aug 17 '18 at 19:15
  • 1
    You can extend `bar` with columns for the words like `'Status'` for example (can be computed but has to be persisted), and then put a foreign key constraint on the number word pair referencing `id` and `type` in `foo`(possibly after declaring the pair primary key or unique in `foo`). Otherwise you'd have to create a trigger on `bar` that check it the way you want. – sticky bit Aug 17 '18 at 19:17
  • 2
    Just adding my 2 cents, but this is a bad design. Create a `type`, `status` and `state` table; create a proper FK to them, let SQL do its job, and call it a day. Anything you do to make this raise errors when invalid combinations are entered will be a hack. – Dave C Aug 17 '18 at 20:18
  • Hi. A FK constraint says subrows somewhere have to appear elsewhere as PK or UNIQUE NOT NULL. FK & key are not verbs so please explain what you want without using them. Use enough words & sentences to clearly say what you mean. Read & act on [mcve]. PS Time to read a textbook on basics of information modeling & database design. Then read re pivoting & pros & cons of EAV. – philipxy Aug 17 '18 at 21:49

1 Answers1

0

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.

J Sidhu
  • 677
  • 1
  • 4
  • 19