|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:
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.
|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.|
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:
|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:
|Preservation of Integrity Constraints|
These procedures preserve integrity constraints when their operation results in a copy of the original data file:
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:
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.
|Procedure||Condition||Integrity Constraints Preserved in Data Sets||Integrity Constraints Preserved in Libraries|
|APPEND||DATA= data set does not exist||General||Not applicable|
|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
|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:
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.
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.
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:
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;
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:
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;
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;
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;
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;
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.