SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

Creating and Using Integrity Constraints in a

Table

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

following:

• nonmissing

• unique

• 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

key.

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

proc sql;

create table sql.mystates

(state char(15),

population num,

continent char(15),

/* 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

create table sql.uspostal

(name char(15),

code char(2) not null, /* constraint specified as */

/* a column attribute */

constraint for_key foreign key(name) /* links NAME to the */

references sql.mystates /* primary key in MYSTATES */

on delete restrict /* forbids deletions to STATE */

/* unless there is no */

/* matching NAME value */

on update set null); /* allows updates to STATE, */

/* changes matching NAME */

/* values to missing */

The DESCRIBE TABLE CONSTRAINTS statement shows only the table constraint

specifications in the Results window.

proc sql;

describe table sql.mystates;

describe table constraints sql.uspostal;

Output 4.13 PROC SQL DESCRIBE TABLE CONSTRAINTS Results Window Shows Integrity Constraints

Integrity constraints cannot be used in views. For more information about integrity

constraints, see SAS Language Reference: Concepts.

130 Chapter 4 • Creating and Updating Tables and Views