1

Lets say i have two tables, one for products and other for OrderDetails. I'm trying to make a check constraint that checking whether the user had inputted a number of quantity inside the OrderDetails table that has exceeded the number of quantity inside the Product table or not.

I tried to use a REFERENCES clause to refer to the other table but i didn't work. and i guess its only working for the foreign key constraint. So, if there's any other ways provided to make the check constraint work that way i would be thankful.

alter table orderdetails add constraint quantity_chck check (quantity <= references products(quantity)) ;
maghraby
  • 33
  • 5
  • 1
    What you are asking about exists in the SQL standard, it's called "assertions" (technical name for multi-row and multi-table constraints). Unfortunately, the basic research into assertions is not very advanced, and in practice, NO vendor supports assertions (even though they are in the standard). The problem is very difficult. There are two main approaches: materialized views and a combination of triggers. See for example http://harmfultriggers.blogspot.com/ and the links within. –  Jan 30 '21 at 02:04

1 Answers1

0

No, you can't do this with a check constraint. Check constraints should hold true all the time. What happens if you insert a row, then tomorrow someone comes along and reduces the quantity in your product table? You'd be in violation of the constraint.

It sounds like what you want to do is check that there is enough quantity in the products table to fill the order, so you want to check if quantity is enough at this moment in time. You could do that with a trigger and raise an exception if the quantity is too low.

create or replace trigger mytrigger 
before insert on orderdetails
for each row
declare
    l_quantity product.quantity%type;
begin
    select p.quantity into l_quantity
      from product p
     where p.product_id = :new.product_id;

    if l_quantity < :new.quantity then
        raise_application_error( -20001, 'Insufficient quantity in the products table' );
    end if;
end;

Triggers are often frowned on. You could also do this with a procedure that did the same and always insert a row into your table by invoking the procedure.

eaolson
  • 14,717
  • 7
  • 43
  • 58
  • The trigger thing wouldn't work in a multi-user environment. There are two valid approaches to the OP's problem; none of them has anything to do with what you described. –  Jan 30 '21 at 02:02