Ensuring Data Integrity in Tables With Constraints

With Oracle Database XE, you can define integrity constraints to enforce business rules on data in your tables to preserve the integrity of the data. Business rules specify conditions and relationships that must always be true, or must always be false. For example, in a table containing employee data, the employee e-mail column must be unique. Similarly, in this table you cannot have two employees with the same employee ID.

When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that inserts or modifies data in the table, Oracle Database XE ensures that the new data satisfies the integrity constraint, without the need to do any checking within your program. Any attempt to insert, update, or remove a row that violates a constraint results in an error, and the statement is rolled back. Likewise, any attempt to apply a new constraint to a populated table also results in an error if any existing row violates the new constraint.

Constraints can be created and, in most cases, modified with a number of different status values. The options include enabled or disabled, which determine if the constraint is checked when rows are added, modified, or removed; and deferred or immediate, which cause constraint validation to occur at the end of a transaction or at the end of a statement, respectively.

You can enforce rules by defining integrity constraints more reliably than by adding logic to your application. Oracle Database XE can check that all the data in a table obeys an integrity constraint faster than an application can.

Constraints can be defined at the column level or at the table level:

Column-level constraints are syntactically defined where the column to which the constraint applies is defined. These constraints determine what values are valid in the column. When creating a table with Object Browser, the only constraint defined at the column level is the NOT NULL constraint, which requires that a value is included in this column for every row in the table.
Table-level constraints are syntactically defined at the end of the table definition and apply to the entire table. With Object Browser, you can create primary key, foreign key, unique, and check constraints.

This section contains the following topics:

Column Default Value
NOT NULL Constraint
Check Constraint
Unique Constraint
Primary Key Constraint
Foreign Key Constraint

Column Default Value

You can define default values that are values that are automatically stored in the column whenever a new row is inserted without a value being provided for the column. When you define a column with a default value, any new rows inserted into the table store the default value unless the row contains an alternate value for the column. Assign default values to columns that contain a typical value. For example, in the employees table, if most employees work in the sales department, then the default value for the department_id column can be set to the ID of the sales department.

Depending on your business rules, you might use default values to represent zero or FALSE, or leave the default values as NULL to signify an unknown value. Default values can be defined using any literal, or almost any expression including SYSDATE, which is a SQL function that returns the current date. For an example of the use of the DEFAULT column value, see Example: Creating a Table With NOT NULL Constraints Using SQL.

NOT NULL Constraint

The NOT NULL constraint is a column-level constraint that requires that the column must contain a value whenever a row is inserted or updated. The NOT NULL constraint must be defined as part of the column definition.

Use a NOT NULL constraint when the data is required for the integrity of the database. For example, if all employees must belong to a specific department, then the column that contains the department identifier should be defined with a NOT NULL constraint. On the other hand, do not define a column as NOT NULL if the data might be unknown or might not exist when rows are added or changed, for example, the second, optional line in a mailing address.

A primary key constraint automatically adds a NOT NULL constraint to the columns included in the primary key, in addition to enforcing uniqueness among the values.

For an example of the use of the NOT NULL constraint, see "Creating a Table".

Check Constraint

A check constraint requires that a column (or combination of columns) satisfies a condition for every row in the table. A check constraint must be a Boolean expression that is evaluated using the column value about to be inserted or updated to the row.

Use check constraints when you need to enforce integrity rules based on logical expressions, such as comparisons. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking.

Examples of check constraints include the following:

A check constraint on employee salaries so that no salary value is less than 0.
A check constraint on department locations so that only the locations Boston, New York, and Dallas are allowed.
A check constraint on the salary and commissions columns to prevent the commission from being larger than the salary.

For an example of the use of the check constraint, see "Adding a Check Constraint".

Unique Constraint

A unique constraint requires that every value in a column be unique. That is, no two rows can have duplicate values in a specified column or combination of columns.

Choose columns for unique constraints carefully. The purpose of these constraints is different from that of primary keys. Unique key constraints are appropriate for any column where duplicate values are not allowed. Primary keys identify each row of the table uniquely, and typically contain values that have no significance other than being unique. In the employees table, the email column has a unique key constraint because it is important that the e-mail address for each employee is unique. Note that the email column has a NOT NULL constraint.

Some examples of good unique keys include:

An employee social security number, where the primary key might be the employee number
A truck license plate number, where the primary key might be the truck number
A customer phone number, consisting of the two columns area_code and local_phone, where the primary key might be the customer number
A department name and location, where the primary key might be the department number

For an example of the use of the unique constraint, see "Adding a Unique Constraint".

Primary Key Constraint

A primary key requires that a column (or combination of columns) be the unique identifier of the row and ensures that no duplicate rows exist. A primary key column cannot contain NULL values. Each table can have only one primary key.

Use the following guidelines when selecting a primary key:

Whenever practical, create a sequence number generator to generate unique numeric values for your primary key values. See "Managing Sequences".
Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row of the table.
Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed.
Choose a column that does not contain any null values. A PRIMARY KEY constraint, by definition, does not allow any row to contain a null value in any column that is part of the primary key.
Choose a column that is short and numeric. Short primary keys are easy to type.
Minimize your use of composite primary keys. A composite primary key constraint applies to more than one column. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers.

For an example of the use of the primary key constraint, see "Adding a Primary Key Constraint".

Foreign Key Constraint

Whenever two tables contain one or more common columns, you can enforce the relationship between the tables through a referential integrity constraint with a foreign key. A foreign key requires that all column values in the child table exist in the parent table. The table that includes the foreign key is called the dependent or child table. The table that is referenced is called the parent table.

An example of a foreign key constraint is when the department column of the employees table (child) must contain a department ID that exists in the departments table (parent).

Foreign keys can be made up of multiple columns. Such a composite foreign key must reference a composite primary or unique key of the exact same structure, with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns. You must use the same datatype for corresponding columns in the parent and child tables. The column names do not need to match.

For performance purposes, you might want to add an index to the columns you define in a child table when adding a foreign key constraint. Oracle Database XE does not do this for you automatically. See "Indexes for Use with Constraints" and "Creating an Index".

When you create a foreign key constraint on a table, you can specify the action to take when rows are deleted in the referenced (parent) table. These actions include:

Disallow Delete - Blocks the delete of rows from the referenced table when there are dependent rows in the table.
Cascade Delete - Deletes the dependent rows from the table when the corresponding parent table row is deleted from the referenced table.
Null on Delete - Sets the foreign key column values in the table to null values when the corresponding table row is deleted from the referenced table.

For an example of the use of the foreign key constraint, see "Adding a Foreign Key Constraint".