So far, we have used
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,
winning_bid NUMBER(11,2), W
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
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
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
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
option, you can specify the keywords
to set the starting state of the constraint.
The next lesson shows you how to find information about constraints in the data dictionary.