6 Constraints

How do I…

6.1 Create a primary key constraint?

6.2 Add a foreign key constraint?

6.3 Add a column check constraint?

6.4 Create a table constraint?

6.5 Determine foreign key dependencies?

6.6 Enable, disable, and enforce constraints?

6.7 Add cascading delete functionality?

6.8 Create a sequence to generate a unique number?

6.9 Determine integrity violations?

6.10 Create a deferred constraint?

You can define constraints to enforce business rules on data in your tables and to ensure validity of data. Constraints also provide a means of defining how tables relate to each other. It is possible to enforce business rules programmatically in your application instead of using constraints, but this is a costlier approach. Constraints move much of the work away from the applications to the database. All data in tables must conform to the rules specified by the underlying constraints. An integrity constraint imposed on a table is a watchdog that ensures that SQL statements that modify data in a table satisfy conditions imposed by the constraint. A referential integrity constraint, however, enforces master-detail relationships between tables.

6.1 Create a Primary Key Constraint

A primary key is a set of columns that uniquely identifies the rows in a table. The task of creating a primary key is essential to enforcing referential integrity at the database level. Creating a primary key establishes a unique index on the table, which can increase the performance of applications using the table. This How-To covers the process of creating a primary key on a table.

6.2 Add a Foreign Key Constraint

Referential integrity constraints within Oracle are used to enforce business rules specified in the data model. It is critical that data in the database follow the rules developed when the database was designed. There are also situations when hierarchical relationships within a single table have to be implemented. This How-To covers the topic of creating referential integrity constraints using foreign key constraints.

6.3 Add a Column Check Constraint

Check constraints allow the database to perform important validation tasks. Adding a check constraint to a table ensures that applications will not create invalid data in the table. It also lets the database perform some of the work the application would normally do. This How-To takes you through the different methods used to create column check constraints.

6.4 Create a Table Constraint

A column constraint can enforce rules on the column in which it is defined, whereas, a table constraint can operate on multiple columns in the table. Complex validation logic involving multiple columns can be easily implemented using table constraints. This How-To explains how this is accomplished with the use of table constraints.

6.5 Determine Foreign Key Dependencies

Constraints on tables can be queried from data dictionary views. The USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views provide information about constraints to which a user account has access. This information can be used to determine foreign key dependencies between tables and columns that relate them. This How-To covers the topic of listing integrity constraint definitions in the database.

6.6 Enable, Disable, and Enforce Constraints

Constraints implement rules within the database. There are times, such as during database maintenance or batch operations, that constraints can be temporarily violated. Constraints can be disabled and enabled so that batch operations and system maintenance can be performed. Enforcing a disabled constraint is much faster than enabling it. This How-To explains how constraints are enabled, disabled, and enforced.

6.7 Add Cascading Delete Functionality

A cascading delete functionality is necessary to maintain referential integrity and to ensure data consistency between related tables during row deletes. In a cascade delete, when rows containing referenced key values are deleted, all rows in child tables with dependent foreign key values are also deleted. This How-To covers the topic of adding a cascade delete functionality for complete referential integrity.

6.8 Create a Sequence to Generate a Unique Number

A sequence generates a unique sequential number in an Oracle session. Sequences can be created with a wide variety of options. Numeric primary keys and unique keys can be easily generated using sequences. Various sequence operations such as creating a sequence, altering a sequence, retrieving the sequence value, and listing sequence information are all covered. This How-To explains how a sequence can be used to generate a unique number for a primary key before inserting a record in a table.

6.9 Determine Integrity Violations

In most situations, constraints have to be disabled for batch operations to upload data into tables. In order to enable a constraint after a data load, rows that violate the constraint have to be either deleted or updated. Integrity violating rows in a table can be identified while enabling the constraint. This How-To illustrates how this is handled.

6.10 Create a Deferred Constraint

Constraints are always checked at the end of each SQL statement that modifies data. Oracle8 allows you to defer checking constraints for validity until the end of the transaction. If any data entered during the transaction violates the constraint upon commit, the transaction is rolled back. This How-To discusses the specifics of using deferred constraints for better performance.