Note: For the CHANGE statement in PROC DATASETS, specifying GENNUM=0
refers to the entire generation group.
Using Passwords in a Generation Group
Passwords for versions in a generation group are maintained as follows:
• If you assign a password to the base version, the password is maintained in
subsequent historical versions. However, the password is not applied to any existing
historical versions.
• If you assign a password to a historical version, the password applies to that
individual data set only.
Understanding Integrity Constraints
Definition of Integrity Constraints
Integrity constraints are a set of data validation rules that you can specify in order to
restrict the data values that can be stored for a variable in a SAS data file. Integrity
constraints help you preserve the validity and consistency of your data. SAS enforces the
integrity constraints when the values associated with a variable are added, updated, or
deleted.
There are two categories of integrity constraints: general and referential.
CAUTION:
Do not use operating system tools when managing integrity constraints. This
can cause your data set to become damaged. Instead, use SAS tools such as the
DATASETS procedure or the SQL procedure.
General and Referential Integrity Constraints
General Integrity Constraints
General integrity constraints enable you to restrict the values of variables within a single
file. There are four types of general constraints:
check
limits the data values of variables to a specific set, range, or list of values. Check
constraints can also be used to ensure that the data values in one variable within an
observation are contingent on the data values of another variable in the same
observation.
not null
requires that a variable contain a data value. Null (missing) values are not allowed.
unique
requires that the specified variable or variables contain unique data values. A null
data value is allowed but is limited to a single instance.
primary key
requires that the specified variable or variables contain unique data values and that
null data values are not allowed. Only one primary key can exist in a data file.
626 Chapter 26 • SAS Data Files
Note: A primary key is a general integrity constraint if it does not have any foreign
key constraints referencing it.
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.
The foreign key constraint links the data values of one or more variables in the foreign
key data file, to corresponding variables and values in the primary key data file. Data
values in the foreign key data file must have a matching value in the primary key data
file, or they must be null. When data is updated or deleted in the primary key data file,
the modifications are controlled by a referential action that is defined as part of the
foreign key constraint.
Separate referential actions can be defined for the Update and Delete operations. There
are three types of referential actions:
restrict
prevents the data values of the primary key variables from being updated or deleted
if there is a matching value in one of the foreign key data file's corresponding foreign
key variables. The restrict type of action is the default action if one is not specified.
set null
enables the data values of the primary key variables to be updated or deleted, but
matching data values in the foreign key data files are changed to null (missing)
values.
cascade
enables the data values in the primary key variables to be updated, and also updates
matching data values in the foreign key data files to the same value. The cascade
type of action is supported only for Update operations.
The requirements for establishing a referential relationship are as follows:
• The primary key and foreign key must reference the same number of variables, and
the variables must be in the same order.
• The variables must be of the same type (character or numeric) and length.
• If the foreign key is being added to a data file that already contains data, the data
values in the foreign key data file must either match existing values in the primary
key data file, or the values must be null.
The foreign key data file can exist in the same SAS library as the referenced primary key
data file (intra-libref), or in a different SAS library (inter-libref). However, if the library
that contains the foreign key data file is temporary, the library that contains the primary
key data file must be temporary as well. In addition, referential integrity constraints
cannot be assigned to data files in concatenated libraries.
There is no limit to the number of foreign keys that can reference a primary key.
However, additional foreign keys can adversely impact the performance of Update and
Delete operations.
When a referential constraint exists, a primary key integrity constraint is not deleted
until all foreign keys that reference it are deleted. There are no restrictions on deleting
foreign keys.
Overlapping Primary Key and Foreign Key Constraints
Variables in a SAS data file can be part of both a primary key (general integrity
constraint) and a foreign key (referential integrity constraint). However, there are
Understanding Integrity Constraints 627
restrictions when you define a primary key and a foreign key constraint that use the same
variables:
• The foreign key's update and delete referential actions must both be RESTRICT.
• When the same variables are used in a primary key and foreign key definition, the
variables must be defined in a different order.
For an example, see “Defining Overlapping Primary Key and Foreign Key Constraints”
on page 637.
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 APPEND, COPY, CPORT, CIMPORT, MIGRATE, and
SORT procedures
• in SAS/CONNECT software, the UPLOAD and DOWNLOAD procedures
• PROC APPEND
• for an existing BASE= data file, integrity constraints in the BASE= file are
preserved, but integrity constraints in the DATA= file that is being appended to
the BASE= file are not preserved.
• for a non-existent BASE= data file, general integrity constraints in the DATA=
file that is being appended to the new BASE= file are preserved. Referential
constraints in the DATA= file are not preserved.
• PROC SORT, PROC UPLOAD, and PROC DOWNLOAD, when an OUT= data file
is not specified
• the SAS Explorer window
You can also use the CONSTRAINT= option to control whether integrity constraints are
preserved for the COPY, CPORT, CIMPORT, UPLOAD, and DOWNLOAD procedures.
General integrity constraints are preserved in an active state. The state in which
referential constraints are preserved depends on whether the procedure causes the
primary key and foreign key data files to be written to the same or different SAS
libraries (intra-libref versus inter-libref integrity constraints). Intra-libref constraints are
preserved in an active state. Inter-libref 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 IC REACTIVATE to reactivate the inactive foreign keys.
The following table summarizes the circumstances under which integrity constraints are
preserved.
Table 26.7 Circumstances That Cause Integrity Constraints to Be Preserved
Procedure Condition
Constraints That Are
Preserved
APPEND DATA= data set does not exist General constraints
Referential constraints are not
affected
628 Chapter 26 • SAS Data Files
Procedure Condition
Constraints That Are
Preserved
COPY CONSTRAINT=yes General constraints
Intra-libref constraints are
referential in an active state
Inter-libref constraints are
referential in an inactive state
CPORT/CIMPORT CONSTRAINT=yes General constraints
Intra-libref constraints are
referential in an active state
Inter-libref constraints are
referential in an inactive state
SORT OUT= data set is not specified General constraints
Referential constraints are not
affected
UPLOAD/
DOWNLOAD
CONSTRAINT=yes and
OUT= data set is not specified
General constraints
Intra-libref constraints are
referential in an active state
Inter-libref constraints are
referential in an inactive state
SAS Explorer window General constraints
CAUTION:
Do not use operating system tools when managing integrity constraints. This
can cause your data set to become damaged. Instead, use SAS tools such as the
DATASETS procedure or the SQL procedure.
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 you add a primary key integrity constraint, the existing index must have the
UNIQUE attribute. When you add a foreign key integrity 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.
• The NOMISS index attribute and the not-null integrity constraint have different
effects. The integrity constraint prevents missing values from being written to the
SAS data file and cannot be added to an existing data file that contains missing
values. The index attribute allows missing data values in the data file but excludes
them from the index.
Understanding Integrity Constraints 629
• When any index is created, it is marked as being “owned” by the user, the integrity
constraint, or both. A user cannot delete an index that is also owned by an integrity
constraint and vice versa. If an index is owned by both, the index is deleted only
after both the integrity constraint and the user have requested the index's deletion. A
note in the log indicates when an index cannot be deleted.
Locking Integrity Constraints
Integrity constraints support both member-level and record-level locking. You can
override the default locking level with the CNTLLEV= data set option. For more
information, see the “CNTLLEV= Data Set Option” in SAS Data Set Options:
Reference.
Encryption and Integrity Constraints
There are two types of algorithms that SAS uses for encrypting:
• SAS Proprietary encryption is implemented with the ENCRYPT=YES data set
option.
• AES (Advanced Encryption Standard) encryption is implemented with the
ENCRYPT=AES data set option.
SAS Proprietary encryption has no restrictions when using integrity constraints.
AES encryption requires that all primary key and foreign key data files must use the
same encryption key that opens all referencing foreign key and primary key data files.
You must specify the ENCRYPTKEY= data set option when using ENCRYPT=AES.
For more information, see “ENCRYPT= Data Set Option” in SAS Data Set Options:
Reference and “ENCRYPTKEY= Data Set Option” in SAS Data Set Options: Reference.
If an encryption key was not recorded for the metadata-bound library, then the
encryption key must be the same for the primary key data file and the referencing
encrypted foreign key data file. For more information about metadata-bound libraries,
see “Metadata-Bound Library” in Base SAS Procedures Guide.
Specifying Integrity Constraints
You can create integrity constraints in the SQL procedure, the DATASETS procedure, or
in SCL (SAS Component Language). The constraints can be specified when the data file
is created or can be added to an existing data file. When you add integrity constraints to
an existing file, SAS verifies that the existing data values conform to the constraints that
are being added.
When you specify integrity constraints, you must specify a separate statement for each
constraint. In addition, you must specify a separate statement for each variable to which
you want to assign the not-null integrity constraint. When multiple variables are included
in the specification for a primary key, foreign key, or a unique integrity constraint, a
composite index is created and the integrity constraint enforces the combination of
variable values. The relationship between SAS indexes and integrity constraints is
described in “Indexes and Integrity Constraints” on page 659. For more information,
see “Understanding SAS Indexes” on page 638.
When you add an integrity constraint in SCL, open the data set in utility mode. See
“Creating Integrity Constraints By Using SCL” on page 633 for an example. Integrity
constraints must be deleted in utility open mode. For detailed syntax information, see
SAS Component Language: Reference.
630 Chapter 26 • SAS Data Files
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.