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:
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.