Implementing Data Integrity Constraints

Data integrity constraints can take on different forms. These constraints can provide you and your users with a level of security in the viability of the data that would likely not be available without their use.

Foreign Keys

Whenever you are creating relationships between two tables that have an implied many-to-one relationship (employee-dependent, manager-employee, order-item), foreign key relationships are usually the manner used to ensure that the relationship remains intact. You will need to consider, both from a technical perspective and from a business rules perspective, several factors when deciding how to maintain the tables involved in the relationship.

DDL on Parent Table

Any time that DDL is done on the parent table, considerations concerning the constraints have to be taken into account.

Whenever you decide that you need to drop a parent table, you need to drop the foreign key constraint before you can complete the parent table drop. This can be accomplished in a single statement:

 DROP TABLE tablename CASCADE CONSTRAINTS 

Whenever you decide that you need to truncate the parent table, you need to either drop the foreign key constraint or at least disable it.

If you are going to drop a tablespace that contains the parent table, the foreign key constraint must be dropped before you can drop the tablespace. Again, this can be accomplished in a single statement:

 DROP TABLESPACE tablespace_name INCLUDING CONTENTS CASCADE CONSTRAINTS 

If you don't use DROP with the CASCADE CONSTRAINTS option, whenever rows are deleted from the parent table, Oracle locks the child table or tables that have foreign key relationship to the parent to ensure that there are no rows remaining in the child table that would have corresponded to the parent table rows being deleted. This ensures that you don't have garbage in your databasefor example, ordered items with no order to associate them to, or dependents with no employee to whom they are tied. Updates to the parent's key are permitted only when no child rows are tied to the old value. If no index exists on the foreign key in the child table, Oracle locks the entire child table to prevent any changes that would affect referential integrity. If there is an index on the foreign key, Oracle locks only the index entries, thereby avoiding more restrictive locks on the entire child table.

If you determine that both tables need to be updated concurrently but from different transactions, it is best to create an index on the foreign key columns.

Whenever data is inserted into the child table (the table with the foreign key constraint) or whenever the foreign key column is updated, Oracle checks the index on the parent table that is being used for enforcing the referenced key. It is, therefore, critical that the tablespace of the parent table's index, but not necessarily the tablespace containing the parent table, be online whenever inserts or updates are being done on the child table.

Defining Constraints at Table Creation Time

Constraints can be defined either when a table is being created or by altering the table to create it. The constraint clause in the CREATE TABLE or the ALTER TABLE statement is used to define the constraint on the table. Certain privileges must be in place to define an integrity constraint on a given set of tables. Either the user defining the constraint needs to own the parent table (it must be in that user's schema) or the user must have REFERENCES privileges on the columns of the referenced key in the parent table.

The generic format for the CREATE TABLE with CONSTRAINT included follows:

 CREATE TABLE table_name ( Column_name column_datatype [CONSTRAINT constraint_name] {[NOT] NULL |UNIQUE [USING INDEX index_clause] |PRIMARY KEY [USING INDEX index_clause] |REFERENCES [schema.]table (column) [ON DELETE CASCADE] |CHECK (conditions)} [NOT DEFERRABLE| DEFERRABLE [INITIALLY {IMMEDIATE|DEFERRED}]] [DISABLE|ENABLE [VALIDATE| NOVALIDATE]]) 

The following list explains these terms and commands:

• CONSTRAINT identifies the integrity constraint by the name supplied, and that information is stored in the data dictionary.

• USING INDEX means that the parameters defined in the clause should be used for the index that Oracle uses to enforce the unique or primary key constraint. The name of the index is the same as the name of the constraint.

• DEFERRABLE indicates that the constraint checking can be deferred until the end of the transaction by using the SET CONSTRAINT command.

• NOT DEFERRABLE indicates that the constraint is checked at the end of every DML statement and cannot be deferred by sessions or transactions. This is the default.

• INITIALLY IMMEDIATE says that, the constraint is checked at the end of every DML statement. If no INITIALLY clause is specified, INITIALLY IMMEDIATE is implied by default.

• INITIALLY DEFERRED says that the constraint is DEFERRABLE and that the constraint will only be checked, by default, at the end of every transaction.

• DISABLE disables the integrity constraint, and Oracle will not enforce it until it is ENABLED.

It is considered good practice to adopt a standard naming convention for constraints; this is particularly true for check constraints because the same constraint can be created several times with different names.

Table Constraints

A table constraint is a constraint that is a part of the table definition and is considered to be a part of the table rather than simply a consideration on the column. It can define any type of constraint except a NOT NULL constraint and takes the previously defined format.

Table constraints are needed when a constraint names two or more columns or when a table is altered to add any constraint other than the NOT NULL constraint.

You can define the NOT NULL constraint after creating the table as follows:

 ALTER TABLE table_name MODIFY column CONSTRAINT constraint_name NOT NULL; 

You can define other constraints after table creation as in the following example:

 ALTER TABLE items ADD CONSTRAINT orders_items_fk FOREIGN KEY (order_no) REFERENCES orders (order_no) DEFERRABLE INITIALLY DEFERRED; 

Now that we have defined constraints on the table and on individual columns (NOT NULL), we need to turn our attention to addressing the maintenance of the constraints.