Using SAS Tables

Preserving the Integrity of Table Data in SCL Programs

SCL provides a group of functions that specify and enforce integrity constraints for SAS tables. Integrity constraints preserve the consistency and correctness of stored data, and they are automatically enforced for each addition, update, and deletion activity for a SAS table to which the constraints have been assigned. For such a table, value changes must satisfy the conditions that have been specified with constraints.

There are two basic types of integrity constraints: general constraints and referential constraints. The following list shows the specific types of integrity restraints that you can apply through SCL. The first four items are general constraints, which control values in a single SAS table. The last item is a referential constraint, which establishes a parent-child relationship between columns in two or more SAS tables.

A column can contain only non-null values.
A column can contain only values that fall within a specific set, range, or list of values, or that duplicate a value in another column in the same row.
A column can contain only values that are unique.
A column that is a primary key can contain only values that are unique and that are not missing values.
A column that is a foreign key (the child) can contain only values that are present in the associated primary key (the parent) or null values. A column that is a primary key can contain only values that cannot be deleted or changed unless the same deletions or changes have been made in values of the associated foreign key. Values of a foreign key can be set to null, but values cannot be added unless they also exist in the associated primary key.

SCL provides the following functions for creating and enforcing integrity constraints:

ICCREATE: creates and specifies integrity constraints for a SAS table.
ICDELETE: drops an integrity constraint.
ICDESCRIBE: obtains the attributes for all existing integrity constraints within a SAS table. You can also obtain integrity constraint information using the CONTENTS procedure.
ICTYPE: returns the type of constraint that is assigned to a SAS table.
ICVALUE: returns the varlist or WHERE clause that is associated with an integrity constraint.

For more information about integrity constraints, see Understanding Integrity Constraints in SAS Language Reference: Concepts .

Top of Page