What are the different ways of adding a constraint so that only items that are available on the order date can be inserted?

order.date must be between item.date_from and item.date_to... what are the different ways of doing that?

CREATE TABLE "item" (
  "id" SERIAL PRIMARY KEY,
  "date_from" DATE NOT NULL,
  "date_to" DATE NOT NULL
);

CREATE TABLE "order" (
  "id" SERIAL PRIMARY KEY,
  "date" DATE NOT NULL
);

CREATE TABLE "order_item" (
  "order" INTEGER NOT NULL REFERENCES "order",
  "item" INTEGER NOT NULL REFERENCES "item" 
);

Solution

Check constraints work on simple expressions. For example, a simple sanity check on the order: check( date > '2010-01-01'). There's also exclusion constraints which check no two rows have the same value as defined by the exclusion. But, with the exception of foreign key constraints, constraints don't query other tables.

You can solve this with a trigger on insert and update, and I'll go into that below, but its better to solve this sort of problem with referential integrity. However, I can't think of a way to do that.

You can make a view of available items for the order. Here $1 is the date of the order.

create temporary view items_available_to_order
  select *
  -- pluralize table names to avoid conflicting with keywords and columns
  from items
  -- date_from and date_to has become a single daterange when_available
  where items.when_available @> $1

Then only insert items from that view.

If you want to go the trigger route (you can do both) write a function which checks whether an order's item is valid. It either raises an exception or returns a trigger. new is the inserted row, or the row after an update.

I changed some of the table and column names and types to avoid common pitfalls.

create function check_item_order_is_valid()
  returns trigger
  language 'plpgsql'
as $body$
declare
  item_is_available boolean;
begin
  select
    items.when_available @> orders.ordered_on into item_is_available
  from item_orders
  join items on items.id = new.order_id
  join orders on orders.id = new.item_id;

  if( not item_is_available) then
    raise exception 'Item #% is not available for order #%',
      new.item_id, new.order_id;
  end if;

  return new;
end
$body$

Then define a trigger to call the function when rows are inserted or updated in the item/order table.

create trigger check_item_orders
  before insert or update
  on item_orders
  for each row
  execute function check_item_order_is_valid();

Demonstration.

What if the valid range of an item changes? You need an update trigger on item to check that its orders are still valid. Maybe. Depends on your business logic.