Integrity Constraints

Definition of Integrity Constraints

Integrity constraints are a set of data validation rules that you can specify to restrict the data values accepted into a SAS data file. Using integrity constraints can preserve the correctness and consistency of stored data. SAS enforces the integrity constraints each time data is changed or deleted in a variable that has integrity constraints assigned to it.

There are two categories of integrity constraints:

General constraints, which allow you to restrict the data values that are accepted for the variables in a single data file, such as requiring that the data values for a variable be unique and/or nonmissing, or making the data values in one variable contingent on the data values in another variable.
Referential constraints, which allow you to link the data values of the variables in one data file to specific variables in another data file. An example of a referential constraint would be linking the values for an employee name variable in a Personnel data file to a similar variable in a Payroll data file and to an Employee Bonuses data file. Only the names of employees that exist in the Personnel data file would be allowed in the Payroll and Employee Bonuses data files.

Note: In SAS, the term "data set" is used to refer to both SAS files, which contain data and data set descriptor information, and to SAS data views, which consist entirely of descriptor information. Because they are associated with stored data, integrity constraints can only be defined in SAS data files. [cautionend]

General Integrity Constraints

There are four types of general integrity constraints:

Check limits the data values in a variable to a specific set, range, or list. This constraint can also be used to make the data values in one variable contingent on the data values in another variable.

Not Null requires that a variable contain a data value. Missing values for character and numeric data are not allowed.

Unique requires that the specified variables contain unique data values.

Primary Key requires that the specified variables contain unique data values and that missing or null data values are not allowed. A data file can have only one primary key.

Referential Integrity Constraints

A referential integrity constraint is created when a primary key integrity constraint in one data file is referenced by a foreign key integrity constraint in another data file.

A foreign key integrity constraint links the data values of one or more variables in its data file to those of the variables specified in a primary key, and controls the action that can be taken when an attempt is made to update or delete the data values in the primary key. The following referential actions can be specified:

RESTRICT prevents the data values in the primary key from being updated or deleted unless there is no matching value in any referencing foreign key variables. This is the default if no referential action is specified.

NULL allows primary variables to be updated or deleted, but changes any affected foreign key values to a missing value.

For example:

proc sql;                       
  create table one
    (
     name   char(14),
     CONSTRAINT prim_key  primary key(name)
     );

proc sql;
   create table two
   (
    lname   char(14),
    CONSTRAINT for_key  foreign key(lname) references one
      on delete restrict on update set null
    );

The preceding example creates a referential integrity constraint between variable Name in table ONE and variable Lname in table TWO. As the primary key, variable Name will define the acceptable data values for variable Lname. In addition, the foreign key specifies that data values will not be deleted from variable Name unless no matching values exist in variable Lname, and updates will cause affected data values in Lname to be changed to a missing value. The primary key integrity constraint also cannot be deleted until this and any other foreign key integrity constraint that references it has been deleted. There are no restrictions on deleting foreign key constraints.

The following rules must be met for a referential relationship to be established:

The primary key and foreign key specifications must reference the variables in the same order.
The variables must be of the same type (character or numeric) and length.

If the referential integrity constraint is being added to existing variables, the data values in the foreign key must match the values in the primary key or be null. For example, using the example above, if primary key variable Name contained the data values shown below, then foreign key variable Lname could have any of the data values shown below except those in column 4.

Potential Foreign Key Data Values for Variable "lname"
Data Values in Primary Key name 1 2 3 4

Davis, Jan Smith, Mike Davis, Jan . Davis, Jan

Smith, Mike Davis, Jan Smith, Mike . Smith, Mike

Smith, Mike . Johnson, Ed

***Potential Foreign Key Data Values for Variable "lname"***
Data Values in Primary Key name	1	2	3	4
Davis, Jan	Smith, Mike	Davis, Jan	.	Davis, Jan
Smith, Mike	Davis, Jan	Smith, Mike	.	Smith, Mike
		Smith, Mike	.	Johnson, Ed

. = missing value

Note that the variable names in the primary key and foreign key specification can match.

A referential integrity constraint can exist between data files in the same or different SAS libraries with these restrictions:

If the library of a data set containing a foreign key integrity constraint is temporary, then the library containing the primary key data set must be temporary as well.
Referential integrity constraints cannot be assigned to data sets in concatenated libraries.

Preservation of Integrity Constraints

These procedures preserve integrity constraints when their operation results in a copy of the original data file:

in base SAS software, the COPY, CPORT, CIMPORT and SORT procedures
in SAS/CONNECT software, the UPLOAD and DOWNLOAD procedures
PROC APPEND, when a DATA= data file does not exist
PROC SORT and PROCs UPLOAD and DOWNLOAD, when an OUT= data file is not specified.

You can use the CONSTRAINT option to control when integrity constraints are preserved for the COPY, CPORT, and CIMPORT procedures, which always result in a copy, and additionally for the UPLOAD and DOWNLOAD procedures.

Several factors affect which integrity constraints are preserved:

the nature of the procedure
whether the procedure is performed on a data file or a library
for referential integrity constraints, whether the integrity constraint exists between data files in the same or different libraries (intra-libref versus inter-libref integrity constraints).
Inter-libref referential integrity constraints are preserved in an inactive state. That is, the primary key portion of the integrity constraint is enforced as a general integrity constraint but the foreign key portion is inactive. You must use the DATASETS procedure statement INTEGRITY CONSTRAINT REACTIVATE to reactivate the inactive foreign key constraint.

The following table summarizes the circumstances under which integrity constraints are preserved.

Circumstances under Which Integrity Constraints are Preserved
Procedure Condition Integrity Constraints Preserved in Data Sets Integrity Constraints Preserved in Libraries

APPEND DATA= data set does not exist General Not applicable

COPY CONSTRAINT= yes General General

Intra-libref is referential

Inter-libref is referential in an inactive state

CPORT/ CIMPORT CONSTRAINT= yes General General

Intra-libref is referential

Inter-libref is referential in an inactive state

SORT OUT= data set is not specified General Not applicable

Intra-libref is referential

Inter-libref is referential in active state

UPLOAD/ DOWNLOAD CONSTRAINT= yes
and OUT= data set is not specified
General General

Intra-libref is referential Intra-libref is referential

Inter-libref is referential in an inactive state Inter-libref is referential in an inactive state

***Circumstances under Which Integrity Constraints are Preserved***
Procedure	Condition	Integrity Constraints Preserved in Data Sets	Integrity Constraints Preserved in Libraries
APPEND	DATA= data set does not exist	General	Not applicable
COPY	CONSTRAINT= yes	General	General
Intra-libref is referential
Inter-libref is referential in an inactive state
CPORT/ CIMPORT	CONSTRAINT= yes	General	General
Intra-libref is referential
Inter-libref is referential in an inactive state
SORT	OUT= data set is not specified	General	Not applicable
Intra-libref is referential
Inter-libref is referential in active state
UPLOAD/ DOWNLOAD	CONSTRAINT= yes and OUT= data set is not specified	General	General
Intra-libref is referential	Intra-libref is referential
Inter-libref is referential in an inactive state	Inter-libref is referential in an inactive state

Indexes and Integrity Constraints

The unique, primary key, and foreign key integrity constraints store data values in an index file. If an index file already exists, it is used; otherwise, one is created. Consider the following points when you create or delete an integrity constraint:

When a user-defined index exists, the index's attributes must be compatible with the integrity constraint in order for the integrity constraint to be created. For example, when adding a primary key constraint, the existing index must have the UNIQUE attribute. When adding a foreign key constraint, the index must not have the UNIQUE attribute.
The unique integrity constraint has the same effect as the UNIQUE index attribute; therefore, when one is used, the other is not necessary.
Although they might appear to be the same, the NOMISS index attribute and not null integrity constraint have different effects. The integrity constraint prevents missing values in a SAS data file and cannot be added to an existing data file with missing values. The index attribute allows missing data values in the data file but excludes them from the index.
When any index is created, it is marked as being "owned" by the user and/or by the integrity constraint. A user cannot remove an index owned by an integrity constraint and an integrity constraint cannot remove an index owned by a user. If an index is owned by both, then the index will be removed only after both the integrity constraint and the user have requested the index's removal. A note in the log indicates when an index could not be removed.

Locking

Integrity constraints support both member-level and record-level locking. You can override the default locking level with the CNTLLEV= data set option. Refer to the SAS Language Reference: Dictionary for more information on CNTLLEV=.

Specifying Integrity Constraints

You create integrity constraints in the SQL procedure, the DATASETS procedure, or in SCL (SAS Component Language). The constraints can be provided when the data file is created or added to an existing SAS data file. When integrity constraints are added to an existing data file, SAS verifies that the data in the variables to which integrity constraints have been assigned conform to the constraints before the integrity constraints are added.

When specifying integrity constraints, note that you must specify a separate statement for each variable that you want to have the not null integrity constraint. When multiple variables are included in the specification for a primary key, foreign key, or unique integrity constraint, a composite index is created and the integrity constraint will enforce the combination of variable values. The relationship between SAS indexes and integrity constraints is described in Indexes and Integrity Constraints. For more information, see SAS Indexes.

When adding an integrity constraint with SCL, open the data set in utility mode. See Example 3: Creating Integrity Constraints with SCL for an example. Integrity constraints must be deleted in utility open mode. For detailed syntax information, see SAS Screen Control Language: Reference.

When generation data sets are used, you must create the integrity constraints in each data set generation that includes protected variables.

Listing Integrity Constraints

The CONTENTS and DATASETS procedures report integrity constraint information as part of normal processing. For PROC SQL, the commands DESCRIBE TABLE and DESCRIBE TABLE CONSTRAINTS report integrity constraint specifications as part of the data file definition or alone, respectively. SCL provides the ICTYPE and ICVALUE functions for getting information about integrity constraints. Refer to the appropriate documentation for syntax information.

Rejected Observations

You can customize the error message for an integrity constraint by using the MESSAGE= option of the PROC DATASETS ICCREATE statement. For more information, see the full description of the DATASETS procedure in the SAS Procedures Guide.

Rejected observations can be collected in a special file using the audit trail functionality.

Examples

Example 1: Creating Integrity Constraints with the DATASETS Procedure

The following sample code creates integrity constraints using the DATASETS procedure. The data file, TV_SURVEY, checks the percentage of viewing time spent on networks, PBS, and other channels, with the following integrity constraints:

the viewership percentage cannot exceed 100 percent
only adults can participate in the survey
"sex" can be male or female.

data tv_survey(label='Validity checking');
  length idnum age 4 sex $1;
  input idnum sex age network pbs other;
  datalines;
1 M 55 80 . 20
2 F 36 50 40 10
3 M 42 20 5 75
4 F 18 30 0 70
5 F 84 0 100 0
;

proc datasets nolist;
  modify tv_survey;
    ic create val_sex = check(where=(sex in ('M','F')))
       message = "Valid values for variable SEX are 
       either 'M' or 'F'.";
    ic create val_age = check(where=(age >= 18 and age <= 120))
       message = "An invalid AGE has been provided.";
    ic create val_new = check(where=(network <= 100));
    ic create val_pbs = check(where=(pbs <= 100));
    ic create val_ot = check(where=(other <= 100));
    ic create val_max = check(where=((network+pbs+other)<= 100));
quit;

Example 2: Creating Integrity Constraints with the SQL Procedure

The following sample program creates integrity constraints using the SQL procedure. The data file PEOPLE lists employees and contains employment information. The data file, SALARY, contains salary and bonus information. The integrity constraints are as follows:

The names of employees receiving bonuses must be found in the PEOPLE data file.
The names identified in the primary key must be unique.
Gender can be male or female.
Job status can be permanent, temporary, or terminated.

proc sql;
   create table people
    (
     name      char(14),
     gender    char(1),
     hired     num,
     jobtype   char(1) not null,
     status    char(10),

    constraint prim_key primary key(name),
    constraint gender check(gender in ('male' 'female')),
    constraint status check(status in ('permanent' 
                            'temporary' 'terminated')) 
     );

     create table salary
     (
      name     char(14),
      salary   num not null,
      bonus    num,

      constraint for_key foreign key(name) references people
         on delete restrict on update set null
     );
  quit;

Example 3: Creating Integrity Constraints with SCL

To add integrity constraints to a data file with SCL, you must create and build an SCL catalog entry. The following sample program creates and compiles catalog entry EXAMPLE.IC_CAT.ALLICS.SCL.

INIT:
  put "Test SCL integrity constraint functions start."; 
return;

MAIN:
   put "Opening WORK.ONE in utility mode.";
   dsid = open('work.one', 'V');/* Utility mode.*/
   if (dsid = 0) then
     do;
      _msg_=sysmsg();
      put _msg_=;
     end;
     else do;
      if (dsid > 0) then
          put "Successfully opened WORK.ONE in" 
              "UTILITY mode.";
     end;

   put "Create a check integrity constraint named teen.";
   rc = iccreate(dsid, 'teen', 'check', 
   '(age > 12) && (age < 20)');

   if (rc > 0) then
      do;
       put rc=;
       _msg_=sysmsg();
       put _msg_=;
     end;
     else do;
      put "Successfully created a check" 
          "integrity constraint.";
     end;

   put "Create a not-null integrity constraint named nn.";
   rc = iccreate(dsid, 'nn', 'not-null', 'age');

   if (rc > 0) then
      do;
       put rc=;
       _msg_=sysmsg();
       put _msg_=;
     end;
     else do;
      put "Successfully created a not-null" 
          "integrity constraint.";
     end;

   put "Create a unique integrity constraint named uq.";
   rc = iccreate(dsid, 'uq', 'unique', 'age');

   if (rc > 0) then
      do;
       put rc=;
       _msg_=sysmsg();
       put _msg_=;
     end;
     else do;
      put "Successfully created a unique" 
          "integrity constraint.";
     end;

  put "Create a primary key integrity constraint named pk.";
  rc = iccreate(dsid, 'pk', 'Primary', 'name');

   if (rc > 0) then
      do;
       put rc=;
       _msg_=sysmsg();
       put _msg_=;
     end;
     else do;
      put "Successfully created a primary key" 
          "integrity constraint.";
     end;
 
   put "Closing WORK.ONE.";
   rc = close(dsid);
   if (rc > 0) then
     do;
      put rc=;
      _msg_=sysmsg();
      put _msg_=;
     end;

   put "Opening WORK.TWO in utility mode.";
   dsid2 = open('work.two', 'V');  
      /*Utility mode */
   if (dsid2 = 0) then
     do;
    _msg_=sysmsg();
      put _msg_=;
     end;
     else do;
      if (dsid2 > 0) then
        put "Successfully opened WORK.TWO in" 
            "UTILITY mode.";
     end;

   put "Create a foreign key integrity constraint named fk.";
   rc = iccreate(dsid2, 'fk', 'foreign', 'name', 
   'work.one','null', 'restrict');

   if (rc > 0) then
      do;
       put rc=;
       _msg_=sysmsg();
       put _msg_=;
     end;
     else do;
      put "Successfully created a foreign key" 
          "integrity constraint.";
     end;

 put "Closing WORK.TWO.";
   rc = close(dsid2);
   if (rc > 0) then
     do;
      put rc=;
      _msg_=sysmsg();
      put _msg_=;
     end;
return;

TERM:
  put "End of test SCL integrity constraint" 
      "functions.";
return;

After creating the SCL catalog entry, the following code can be submitted to create two data files, ONE and TWO, and execute SCL entry EXAMPLE.IC_CAT.ALLICS.SCL.

   /* Submit to create data files. */

data one two;
   input name $ age;
cards;
Morris 13
Elaine 14
Tina 15
run;

   /* after compiling, run the SCL program */

proc display catalog= example.ic_cat.allics.scl;
run;

Example 4: Removing Integrity Constraints

The following sample program segments remove integrity constraints. In those that delete a primary key integrity constraint, note that the foreign key integrity constraint is deleted first.

This program segment deletes integrity constraints using PROC SQL.

proc sql;
alter table salary
  DROP CONSTRAINT for_key;
alter table people
    DROP CONSTRAINT gender
    DROP CONSTRAINT _nm0001_
    DROP CONSTRAINT status
    DROP CONSTRAINT prim_key
    ;
quit;

This program segment removes integrity constraints using PROC DATASETS.

proc datasets nolist;
   modify tv_survey;
      ic delete val_max;
      ic delete val_sex;
      ic delete val_age;
run;
quit;

This program segment removes integrity constraints using SCL.

TERM:
   put "Opening WORK.TWO in utility mode.";
   dsid2 = open( 'work.two' , 'V' );  /* Utility mode.     */
   if (dsid2 = 0) then
     do;
     _msg_=sysmsg();
     put _msg_=;
   end;
   else do;
     if (dsid2 > 0) then
        put "Successfully opened WORK.TWO in Utility mode.";
    end;

rc = icdelete(dsid2, 'fk');
if (rc > 0) then
  do;
   put rc=;
   _msg_=sysmsg();
  end;
else
   do;
   put "Successfully deleted a foreign key integrity constraint.";
   end;
rc = close(dsid2);
return;

Example 5: Reactivating an Inactive Integrity Constraint

The following program segment reactivates a foreign key integrity constraint that has been inactivated as a result of a COPY, CPORT, CIMPORT, UPLOAD, or DOWNLOAD procedure.

proc datasets;
   modify data-set;
      ic reactivate fkname references libref;
   run;
quit;

Chapter Contents
Previous
Next
Top of Page

Check	limits the data values in a variable to a specific set, range, or list. This constraint can also be used to make the data values in one variable contingent on the data values in another variable.
Not Null	requires that a variable contain a data value. Missing values for character and numeric data are not allowed.
Unique	requires that the specified variables contain unique data values.
Primary Key	requires that the specified variables contain unique data values and that missing or null data values are not allowed. A data file can have only one primary key.

RESTRICT	prevents the data values in the primary key from being updated or deleted unless there is no matching value in any referencing foreign key variables. This is the default if no referential action is specified.
NULL	allows primary variables to be updated or deleted, but changes any affected foreign key values to a missing value.