Distributed Databases   «Prev  Next»

Set up deferred Constraints on Table

Course project: Set up a deferred constraint on a table

The code you should have created is listed below.
You created a deferrable foreign key constraint:
ALTER TABLE CUSTOMER_PROFILE 
ADD CONSTRAINT CUST_PROFILE_PROD_FK 
FOREIGN KEY(LAST_PRODUCT_PURCHASED) 
REFERENCES PRODUCT_INVENTORY 
DEFERRABLE INITIALLY IMMEDIATE 
/ 
-- You set the constraint to its deferred state: 
SET CONSTRAINT CUST_PROFILE_PROD_FK DEFERRED 
/ 

SET CONSTRAINTS

Syntax Diagram for Oracle Constraints

DESCRIPTION: SET CONSTRAINTS DEFERRED tells Oracle not to check the validity of a constraint until the transaction is committed. This allows you to temporarily defer integrity checking of data. Deferred constraints are usually used when performing DML against multiple related tables when you cannot guarantee the order of the transactions. You must either own the table modified by the constraint or you must have SELECT privilege on the table.
The default setting is SET CONSTRAINTS IMMEDIATE, in which case the constraints are checked as soon as a record is inserted into the table.