Chapter Contents


The DATASETS Procedure

IC CREATE Statement

Creates an integrity constraint.

Restriction: Must be in a MODIFY RUN group

IC CREATE <constraint-name>=constraint <MESSAGE='message-string'>;

Required Arguments

is a name for the constraint. The name must be a valid SAS name. When you do not supply a constraint name, a default name is generated. This default constraint name has the following form

Default name Constraint type
_NMxxxx_ Not Null
_UNxxxx_ Unique
_CKxxxx_ Check
_PKxxxx_ Primary key
_FKxxxx_ Foreign key

where xxxx is a counter beginning at 0001.

Note:   The names PRIMARY, FOREIGN, MESSAGE, UNIQUE, DISTINCT, CHECK, and NOT cannot be used as values for constraint-name.  [cautionend]

is the type of constraint. Valid values are

NOT NULL(variable)
specifies that variable does not contain a SAS missing value, including special missing values.

specifies that the values of variables must be unique. This constraint is identical to DISTINCT.

specifies that the values of variables must be unique. This constraint is identical to UNIQUE.

specifies validity checking with respect to lists and ranges. This is accomplished with a WHERE clause.

PRIMARY KEY(variable)
specifies a primary key variable, that is, a variable that does not contain missing values and whose values are unique.

FOREIGN KEY(variables) REFERENCES table-name <ON DELETE referential-action> <ON UPDATE referential-action>
specifies a foreign key, that is, a set of variables whose values are linked to the values of the primary key variable in another data set. The referential actions are performed when updates are made to the values of a primary key variable that is referenced by a foreign key. For a RESTRICT referential action,

a delete operation
deletes the primary key row, but only if no foreign key values matches the deleted value.

an update operation
updates the primary key value, but only if no foreign keys match the current value to be updated.

For a SET NULL referential action,

a delete operation
deletes the primary key row and sets the corresponding foreign key values to NULL.

an update operation
modifies the primary key value and sets all matching foreign key values to NULL.

"message-string" is the text of an error message that is written to the log when the data fail the constraint. For example,
ic create not null(socsec) 
   message='Invalid Social Security number';
Length: The maximum length of the message is 250 characters.

The following examples show how to create integrity constraints:

ic create a = not null(x);
ic create Unique_D = unique(d);
ic create Distinct_DE = distinct(d e);
ic create E_less_D = check(where (e < d or d = 99));
ic create primkey = primary key(a);
ic create not null (x);

Chapter Contents



Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.