O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required