Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order.

How to impose an additional constraint in DB that says Item should be "Available" (at that point of time) if it needs be to added as item_id in the Order table? An item is manually determined as "Available" and can't be derived from other fields in the database in this scenario.

One schema design (I prefer) is to add a Type column which would have "Available" and "Unavailable" fields. But how can I check the Foreign Key constraint item_id should not only be a Primary Key in Item table, its Type should be "Available" as well?

This Stack Overflow answer using check constraints seems close, but is that the only way? I feel this is a trivial thing for RDBMS, or is this is not a normalized data?

The other schema design (I don't prefer) is to have a Table called "Menu" which could have only the Items Available. The problem is that this table is going to very dynamic in nature and it keeps changing depending on the availability of items. And, I am just creating a subset table out of Items depending on its state which doesn't seem to be a nice idea.

It's easy to do this programmatically; however, how do I achieve this in RDBMS? I like the idea of Database being intelligent enough to handle this.

Original answer is wrong! See Edit 1 for the corrected version.

Original answer

An amazing solution would require a foreign key to a column in a view or a inherited table, but unfortunately PostgreSQL (I suppose that's your RDBMS because of the tag) does not have that (yet).

I think a simple change in the way you organize the data would suffice: create a table like ItemsAvailableQuantity, connecting an Item with its availability which will be references in the orders. When an item is not available anymore, DELETE it from it.

CREATE TABLE Item (
    id   serial  NOT NULL
  , name text    NOT NULL
  , cost numeric

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
    );

CREATE TABLE ItemAvailableQuantity (
    id       serial  NOT NULL
  , item_id  integer NOT NULL
  , quantity integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES Item (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
  , CONSTRAINT postive_quantity -- This constraint is the same as
        CHECK (quantity > 0)    -- checking something like `available = TRUE`.
    );

CREATE TABLE ItemOrder (       -- Changed the name from `Order` because
    id      serial  NOT NULL   -- PostgreSQL refuses that name, somehow
  , bill_id integer NOT NULL
  , item_id integer NOT NULL
  , units   integer NOT NULL

  , PRIMARY KEY (id)
  , FOREIGN KEY (item_id)
        REFERENCES ItemAvailableQuantity (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
-- Uncomment when `Bill` table is ready
--  , FOREIGN KEY (bill_id)
--        REFERENCES Bill (id)
--        ON UPDATE CASCADE
--        ON DELETE CASCADE
  , CONSTRAINT positive_units
        CHECK (units > 0)
    );

Notice! The constraint positive_units may cause problems when your software reduces the units and reaches 0. Make it something like CHECK >= 0 if needed, or add a trigger that automatically DELETE-s rows when units reaches 0 (or less) on each INSERT or UPDATE. This would preserve the table ItemAvailableQuantity to have only actually available items, which is what we want for being referenced from the table ItemOrder.

This should solve your problem. It's not an exact answer to your question. That would involve a trigger or a CHECK calling a function as in the link you provided.

To easily see the quantity of the items then, just create a view that joins ItemAvailableQuantity and Item. If you really want then, make it INSERT-able with a trigger (see yellow-box warning).

Edit 1

Actually Order (a.k.a. ItemOrder) should reference the Item instead of ItemAvailableQuantity to avoid any problem when the Item is not currently available, as stated in the comment.

This suggest we should remove the whole table ItemAvailableQuantity and only add a column available_quantity on Item.

CREATE TABLE Item (
    id                 serial NOT NULL
  , name               text   NOT NULL
  , cost               numeric
  , available_quantity integer NOT NULL

  , PRIMARY KEY (id)
  , CONSTRAINT positive_cost
        CHECK (cost > 0)
  , CONSTRAINT non_negative_quantity
        CHECK (quantity >= 0)
    );

Then, to be certain of inserting only available items into orders we could just run

INSERT INTO ItemOrder (bill_id, item_id, units) VALUES
    (SELECT id FROM Bill WHERE condition = something -- customize at will
    , SELECT id FROM Item WHERE available_quantity >= wanted_quantity
        AND other_condition = something
    , wanted_quantity)
    ;

where wanted_quantity is a parameter passed by your software to the query.

Still, solves the problem, but is no direct answer to the question.

StackExchange Database Administrators Q#132776, answer score: 3