Creating and Using Integrity Constraints in a
Integrity constraints are rules that you specify to guarantee the accuracy, completeness,
or consistency of data in tables. All integrity constraints are enforced when you insert,
delete, or alter data values in the columns of a table for which integrity constraints have
been defined. Before a constraint is added to a table that contains existing data, all the
data is checked to determine that it satisfies the constraints.
You can use general integrity constraints to verify that data in a column is one of the
• both nonmissing and unique
• within a specified set or range of values
You can also apply referential integrity constraints to link the values in a specified
column (called a primary key) of one table to values of a specified column in another
table. When linked to a primary key, a column in the second table is called a foreign
When you define referential constraints, you can also choose what action occurs when a
value in the primary key is updated or deleted.
• You can prevent the primary key value from being updated or deleted when matching
values exist in the foreign key. This is the default.
• You can allow updates and deletions to the primary key values. By default, any
affected foreign key values are changed to missing values. However, you can specify
the CASCADE option to update foreign key values instead. Currently, the
CASCADE option does not apply to deletions.
You can choose separate actions for updates and for deletions.
Note: Integrity constraints cannot be defined for views.
The following example creates integrity constraints for a table, MyStates, and another
table, USPostal. The constraints are as follows:
• state name must be unique and nonmissing in both tables
• population must be greater than 0
• continent must be either North America or Oceania
create table sql.mystates
/* contraint specifications */
constraint prim_key primary key(state),
constraint population check(population gt 0),
constraint continent check(continent in ('North America', 'Oceania')));
Creating and Using Integrity Constraints in a Table 129