How to add foreign key constraint to Table A (id, type) referencing either of two tables Table B (id, type) or Table C (id, type)?

Question

I'm looking to use two columns in Table A as foreign keys for either one of two tables: Table B or Table C. Using columns table_a.item_id and table_a.item_type_id, I want to force any new rows to either have a matching item_id and item_type_id in Table B or Table C.

Example:

Table A: Inventory 
+---------+--------------+-------+
| item_id | item_type_id | count |
+---------+--------------+-------+
|       2 |            1 |    32 |
|       3 |            1 |    24 |
|       1 |            2 |    10 |
+---------+--------------+-------+

Table B: Recipes
+----+--------------+-------------------+-------------+----------------------+
| id | item_type_id |       name        | consistency | gram_to_fluid_ounces |
+----+--------------+-------------------+-------------+----------------------+
|  1 |            1 | Delicious Juice   | thin        | .0048472             |
|  2 |            1 | Ok Tasting Juice  | thin        | .0057263             |
|  3 |            1 | Protein Smoothie  | heavy       | .0049847             |
+----+--------------+-------------------+-------------+----------------------+

Table C: Products
+----+--------------+----------+--------+----------+----------+
| id | item_type_id |   name   | price  | in_stock | is_taxed |
+----+--------------+----------+--------+----------+----------+
|  1 |            2 | Purse    | $200   | TRUE     | TRUE     |
|  2 |            2 | Notebook | $14.99 | TRUE     | TRUE     |
|  3 |            2 | Computer | $1,099 | FALSE    | TRUE     |
+----+--------------+----------+--------+----------+----------+

Other Table: Item_Types
+----+-----------+
| id | type_name |
+----+-----------+
|  1 | recipes   |
|  2 | products  |
+----+-----------+

I want to be able to have an inventory table where employees can enter inventory counts regardless of whether an item is a recipe or a product. I don't want to have to have a product_inventory and recipe_inventory table as there are many operations I need to do across all inventory items regardless of item types.

One solution would be to create a reference table like so:

Table CD: Items
+---------+--------------+------------+-----------+
| item_id | item_type_id | product_id | recipe_id |
+---------+--------------+------------+-----------+
|       2 |            1 | NULL       | 2         |
|       3 |            1 | NULL       | 3         |
|       1 |            2 | 1          | NULL      |
+---------+--------------+------------+-----------+

It just seems very cumbersome, plus I'd now need to add/remove products/recipes from this new table whenever they are added/removed from their respective tables. (Is there an automatic way to achieve this?)

CREATE TABLE [dbo].[inventory] (
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [item_id] [smallint] NOT NULL,
    [item_type_id] [tinyint] NOT NULL,
    [count] [float] NOT NULL,
CONSTRAINT [PK_inventory_id] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

What I would really like to do is something like this...

ALTER TABLE [inventory]  
ADD  CONSTRAINT [FK_inventory_sources] FOREIGN KEY ([item_id],[item_type_id])
REFERENCES {[products] ([id],[item_type_id]) OR [recipes] ([id],[item_type_id])}

Maybe there is no solution as I'm describing it, so if you have any ideas where I can maintain the same/similar schema, I'm definitely open to hearing them! Thanks :)

Answer 1

I think there is a flaw in your database design. The best way to solve your actual problem, is to have Recipies and products as one single table. Right now you have a redundant column in each table called item_type_id. That column is not worth anything, unless you actually have the items in the same table. I say redundant, because it has the same value for absolutely every entry in each table.

You have two options. If you can not change the database design, work without foreign keys, and make the logic layer select from the correct tables.

Or, if you can change the database design, make products and recipies exist in the same table. You already have a item_type table, which can identify item categorization, so it makes sense to put all items in the same table

Answer 2

Since your products and recipes are stored separately, and appear to mostly have separate columns, then separate inventory tables is probably the correct approach. e.g.

CREATE TABLE dbo.ProductInventory
(
        Product_id INT NOT NULL,
        [count] INT NOT NULL,
    CONSTRAINT FK_ProductInventory__Product_id FOREIGN KEY (Product_id) 
        REFERENCES dbo.Product (Product_id)
);

CREATE TABLE dbo.RecipeInventory
(
        Recipe_id INT NOT NULL,
        [count] INT NOT NULL,
    CONSTRAINT FK_RecipeInventory__Recipe_id FOREIGN KEY (Recipe_id) 
        REFERENCES dbo.Recipe (Recipe_id )
);

If you need all types combined, you can simply use a view:

CREATE VIEW dbo.Inventory
AS
    SELECT  Product_id AS item_id,
            2 AS item_type_id,
            [Count]
    FROM    ProductInventory
    UNION ALL
    SELECT  recipe_id AS item_id,
            1 AS item_type_id
            [Count]
    FROM    RecipeInventory;
GO

IF you create a new item_type, then you need to amend the DB design anyway to create a new table, so you would just need to amend the view at the same time

Another possibility, would be to have a single Items table, and then have Products/Recipes reference this. So you start with your items table, each of which has a unique ID:

CREATE TABLE dbo.Items
(
        item_id INT IDENTITY(1, 1) NOT NULL 
        Item_type_id INT NOT NULL,
    CONSTRAINT PK_Items__ItemID PRIMARY KEY (item_id),
    CONSTRAINT FK_Items__Item_Type_ID FOREIGN KEY (Item_Type_ID) REFERENCES Item_Type (Item_Type_ID),
    CONSTRAINT UQ_Items__ItemID_ItemTypeID UNIQUE (Item_ID, Item_type_id)
);

Note the unique key added on (item_id, item_type_id), this is important for referential integrity later on.

Then each of your sub tables has a 1:1 relationship with this, so your product table would become:

CREATE TABLE dbo.Products
(
        item_id BIGINT NOT NULL,
        Item_type_id AS 2,
        name VARCHAR(50) NOT NULL,
        Price DECIMAL(10, 4) NOT NULL,
        InStock BIT NOT NULL,
    CONSTRAINT PK_Products__ItemID PRIMARY KEY (item_id),
    CONSTRAINT FK_Products__Item_Type_ID FOREIGN KEY (Item_Type_ID) 
        REFERENCES Item_Type (Item_Type_ID),
    CONSTRAINT FK_Products__ItemID_ItemTypeID FOREIGN KEY (item_id, Item_Type_ID) 
        REFERENCES dbo.Item (item_id, item_type_id)
);

A few things to note:

• item_id is again the primary key, ensuring the 1:1 relationship.
• the computed column item_type_id (as 2) ensuring all item_type_id's are set to 2. This is key as it allows a foreign key constraint to be added
• the foreign key on (item_id, item_type_id) back to the items table. This ensures that you can only insert a record to the product table, if the original record in the items table has an item_type_id of 2.

A third option would be a single table for recipes and products and make any columns not required for both nullable. This answer on types of inheritance is well worth a read.

Answer 3

you can only add one constraint for a column or pair of columns. Think about apples and oranges. A column cannot refer to both oranges and apples. It must be either orange or apple.

As a side note, this can be somehow achieved with PERSISTED COMPUTED columns, however It only introduces overhead and complexity.

Check This for Reference

Answer 4

You can add some computed columns to the Inventory table:

ALTER TABLE Inventory
    ADD _recipe_item_id AS CASE WHEN item_type_id = 1 THEN item_id END persisted
ALTER TABLE Inventory
    ADD _product_item_id AS CASE WHEN item_type_id = 2 THEN item_id END persisted

You can then add two separate foreign keys to the two tables, using those two columns instead of item_id. I'm assuming the item_type_id column in those two tables is already computed/constraint appropriately but if not you may want to consider that too.

Because these computed columns are NULL when the wrong type is selected, and because SQL Server doesn't check FK constraints if at least one column value is NULL, they can both exist and only one or the other will be satisfied at any time.