Database Reference
In-Depth Information
[NOT] DEFERRABLE . Validation of the constraint can be done at
the end of a transaction instead of immediately when the row is
inserted, updated, or deleted. The default state is NOT DEFERRA-
BLE or immediately. Changing the DEFERRABLE constraint state
requires table re-creation.
INITIALLY { IMMEDIATE | DEFERRED } . This state func-
tions with deferrable constraints only. IMMEDIATE initiates a
check at the end of every SQL command and DEFERRED at the
completion of every transaction. It is only relevant when the con-
straint is also DEFERRABLE. The default state is INITIALLY
IMMEDIATE.
RELY . Applicable to data marts and materialized views in data ware-
housing. When RELY is selected, a NOVALIDATE (nonvalidated)
constraint state specifies a constraint as being valid for query
rewrites, thus materialized views. It is only usable in the ALTER
TABLE command.
USING INDEX clause . Allows index specification for primary and
unique key constraints. Indexing is covered in Chapter 21.
EXCEPTIONS clause . Stores ROWID values for all rows violating
any current constraint states into an exceptions table providing a
record of errors.
Let's try disabling a constraint in the SONGS table. Disable the foreign
key constraint on the ARTIST_ID column.
ALTER TABLE SONGS MODIFY CONSTRAINT FK_ARTISTS DISABLE;
Note: Constraint states can be implemented using both table and view
DDL commands.
20.3.4
Renaming a Constraint
Constraints can be renamed without modification to the table, avoiding
any time-consuming data restructuring or table modifications. In the previ-
ous section on adding constraints to an existing table, we created a unique
constraint named UK_ARTISTS on the ARTISTS table that is a unique
constraint on the NAME column.
Search WWH ::




Custom Search