The first step in setting up deferrable constraints is to drop the existing constraint. You must do this if you have created constraints without specifying DEFERRABLE in the create statement. Here we see a DROP CONSTRAINT command for the primary key constraint for the CUSTOMER_OBJ_TABLE table. Type a slash and press Enter to execute the command.
The constraint was dropped. Now, you must create the primary key constraint, making it a deferrable constraint. We have added the first two lines of the command. You add the next line:
PRIMARY KEY (CUST_ID) DEFERRABLE
Press Enter after typing this line to tell Oracle that the constraint can be set as a deferred constraint.
The final line to type looks like this: INITIALLY IMMEDIATE This tells Oracle that even though the constraint can be deferred, it should be checked immediately for now. Later on (as we will see in this simulation), the constraint can be switched so that it is deferred. Press Enter after typing the line.
Execute the command by typing a slash and pressing Enter.
When you want to set a constraint to the deferred state, you do so for the duration of your session or until you use the SET command to return it to the immediate state. To set one constraint, or a list of constraints, to the deferred state, use a command like this one:
SET CONSTRAINT CUSTOMER_OBJ_PK DEFERRED This command sets the constraint we just created to its deferred state. Type the command and press Enter.
We added the slash to execute the command for you. Instead of listing the constraints to be deferred, you can instruct Oracle to defer all deferrable constraints to their deferred state. To do this, the command is:
SET CONSTRAINT ALL DEFERRED
Type the command and press Enter.
We added the slash to execute the command for you. To reset the constraints, you can either exit this session or issue this command: SET CONSTRAINT ALL IMMEDIATE Type the command and press Enter.
We added the slash to execute the command for you. You have now created a deferrable constraint, set the constraint to the DEFERRED state, and then set it back to the IMMEDIATE state.
