AN ADDITIONAL CONSTRAINT EMP_EMAIL_CK IS ADDED TO THE EMAIL COLUMN...

15. An additional constraint EMP_EMAIL_CK is added to the EMAIL column,

which makes two checks on the e-mail address. The INSTR functions search

for “@” and “.” characters (which will always be present in a valid e-mail

address) and if it can’t find both of them, the check condition will return

FALSE and the row will be rejected.

The preceding examples show several possibilities for defining constraints at table

creation time. Further possibilities not covered include:

• Controlling the index creation for the unique and primary key constraints

• Defining whether the constraint should be checked at insert time (which it is

by default) or later on, when the transaction is committed

• Stating whether the constraint is in fact being enforced at all (which is the

default) or is disabled

It is possible to create tables with no constraints and then to add them later with

an ALTER TABLE command. The end result will be the same, but this technique does

make the code less self-documenting, as the complete table definition will then be

spread over several statements rather than being in one.

Constraint State

At any time, every constraint is either enabled or disabled, and validated or not

validated. Any combination of these is syntactically possible:

• ENABLE VALIDATE It is not possible to enter rows that would violate the

constraint, and all rows in the table conform to the constraint.

• DISABLE NOVALIDATE Any data (conforming or not) can be entered, and

there may already be nonconforming data in the table.

• ENABLE NOVALIDATE There may already be nonconforming data in the

table, but all data entered now must conform.

• DISABLE VALIDATE An impossible situation: all data in the table conforms

to the constraint, but new rows need not. The end result is that the table is

locked against DML commands.

The ideal situation (and the default when a constraint is defined) is ENABLE

VALIDATE. This will guarantee that all the data is valid, and no invalid data can

be entered. The other extreme, DISABLE NOVALIDATE, can be very useful when

uploading large amounts of data into a table. It may well be that the data being

uploaded does not conform to the business rules, but rather than have a large upload

fail because of a few bad rows, putting the constraint in this state will allow the

upload to succeed. Immediately following the upload, transition the constraint into

the ENABLE NOVALIDATE state. This will prevent the situation from deteriorating

further while the data is checked for conformance before transitioning the constraint

to the ideal state.

As an example, consider this script, which reads data from a source table of live

data into a table of archive data. The assumption is that there is a NOT NULL

P AR T II

constraint on a column of the target table that may not have been enforced on

the source table:

alter table sales_archive modify constraint sa_nn1 disable novalidate;insert into sales_archive select * from sales_current;alter table sales_archive modify constraint sa_nn1 enable novalidate;update sales_archive set channel='NOT KNOWN' where channel is null;alter table sales_archive modify constraint sa_nn1 enable validate;

Constraint Checking

Constraints can be checked as a statement is executed (an IMMEDIATE constraint) or

when a transaction is committed (a DEFERRED constraint). By default, all constraints

are IMMEDIATE and not deferrable. An alternative approach to the previous example

would have been possible had the constraint been created as deferrable:

set constraint sa_nn1 deferred;commit;set constraint sa_nn1 immediate;

For the constraint to be deferrable, it must have been created with appropriate

syntax:

alter table sales_archive add constraint sa_nn1check (channel is not null) deferrable initially immediate;

It is not possible to make a constraint deferrable later, if it was not created that

way. The constraint SA_NN1 will by default be enforced when a row is inserted (or

updated), but the check can be postponed until the transaction commits. A common

use for deferrable constraints is with foreign keys. If a process inserts or updates rows

in both the parent and the child tables, if the foreign key constraint is not deferred the

process may fail if rows are not processed in the correct order.

Changing the status of a constraint between ENABLED/DISABLED and VALIDATE/

NOVALIDATE is an operation that will affect all sessions. The status change is a data

dictionary update. Switching a deferrable constraint between IMMEDIATE and

DEFERRED is session specific, though the initial state will apply to all sessions.

EXAM TIP By default, constraints are enabled and validated, and they are not

deferrable.

290