Managing Constraints   «Prev  Next»
Lesson 8Implementing constraints
ObjectiveIdentify how constraints are implemented.

Implementing Table Constraints in Oracle

Table constraints vs. column Constraints

So far, we have used CONSTRAINT syntax that is associated with a particular table. Because most constraints refer to a particular column in a table, you may prefer to assign constraints as part of a column definition instead of the table definition.
Below is an example of an SQL statement with a table constraint.
CREATE TABLE LOT (auction_id NUMBER, 
lot_id NUMBER, 
min_price NUMBER(11,2), 
auction_id NUMBER, 
winning_bid NUMBER(11,2), W
winning_bidder NUMBER,
CONSTRAINT fk_auction_id FOREIGN KEY (auction_id) REFERENCES auction(auction_id));

The SQL statement is equivalent to this definition, with the same constraint on the column:

CREATE TABLE LOT (auction_id NUMBER 
CONSTRAINT fk_auction_id REFERENCES auction(auction_id), 
lot_id NUMBER, min_prince NUMBER(11,2), 
auction_id NUMBER, 
winning_bid NUMBER(11,2), 
winning_bidder NUMBER;


You do not have to explicitly identify the column in a column-level constraint. You must define a table level constraint if the constraint includes more than one column, such as a primary key that is based on two or more columns.

Deferred constraints

When you apply constraints, you have a choice of when you want them executed. You may impose the conditions when the SQL statement that affects how the constrained table is executed. Another choice is to defer the constraint's operation until the end of the transaction that includes the SQL statement that affects the constrained table.
For instance, you may have a FOREIGN KEY constraint on a column, but you may not create the matching value for the primary key it refers to until a later SQL statement. If you defer the implementation of the FOREIGN KEY constraint, you will not get an error, because the required value will exist by the end of the transaction.
You can defer a constraint with the DEFERRABLE keyword as part of the constraint. You can use the
ALTER TABLE SET CONSTRAINTS DEFERRED

to defer the eligible constraints on a table and use the ALTER TABLE SET CONSTRAINTS IMMEDIATE to force all deferred constraints back to immediate operation. When you use the DEFFERABLE option, you can specify the keywords INITIALLY DEFFERRED or INITIALLY IMMEDIATE to set the starting state of the constraint.
The next lesson shows you how to find information about constraints in the data dictionary.