I have one table "inventory" with a column "stock" and another table "bike_with_inventory" with column "input". I'm working with Oracle APEX but that doesn't matter.
I just want to do a constraint check(stock>=input) so that I cant book a part which is not there.
Any suggestions? I can't find anything to do that so help would be appreciated.
I have one table "inventory" with a column "stock" and one table "bike_with_inventory" with column "input".
I just want to do a constraint check(stock>=input) so that I cant book a part which is not there.
You cannot - a CHECK constraint can only reference columns in the same table.
Instead you can wrap the logic in a stored procedure and use that to validate the data before the INSERT.
CREATE PROCEDURE book_part(
i_id IN BIKE_WITH_INVENTORY.ID%TYPE,
i_input IN BIKE_WITH_INVENTORY.INPUT%TYPE,
o_success OUT NUMBER
)
IS
p_stock INVENTORY.STOCK%TYPE;
BEGIN
SELECT stock
INTO p_stock
FROM inventory
WHERE id = i_id;
IF p_stock < i_input THEN
o_success := 0;
RETURN;
END IF;
INSERT INTO bike_with_inventory ( id, input )
VALUES ( i_id, i_input );
UPDATE inventory
SET stock = stock - i_input
WHERE id = i_id;
o_success := 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
o_success := 0;
END;
/
Or you could use a trigger.
I believe you are not thinking about this the right way. You really want a CHECK CONSTRAINT, not a verification at the time of insertion (or update) only.
The constraint should be valid (return TRUE) at all times, and it should prevent "invalid" changes to BOTH tables. One shouldn't be allowed to reduce the quantity in the INVENTORY table without a sufficient reduction in the corresponding quantity in BIKE_WITH_INVENTORY. Doesn't the inequality stock >= input have to be true AT ALL TIMES, and not just at initial insertion into BIKE_WITH_INVENTORY?
One method to implement such check constraints is to create a materialized view with fast refresh on commit. It should have three columns: ID, STOCK and INPUT (selected from the join of the two tables on ID). On the materialized view, you can have check constraints - in this case it would be STOCK >= INPUT.
The MV will cause transactions to fail at COMMIT time - which is bad in one sense (you don't get immediate feedback) and good in another (you can make changes to both tables, and if the end result after the FULL transaction or transactions is valid, then you can COMMIT and the transactions will be successful).
I won't show an illustration of how this should work here; do a Google search for "materialized view to implement multi-table constraint" and see what comes back.
