DATASETS Procedure

IC CREATE Statement

Creates an integrity constraint.

Restriction:	Must be in a MODIFY RUN group
Note:	In order for a referential constraint to be established, the foreign key must specify the same number of variables as the primary key, in the same order, and the variables must be of the same type (character/numeric) and length.
See:	Understanding Integrity Constraints in SAS Language Reference: Concepts

Syntax

Required Argument

Optional Arguments

Syntax

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

Required Argument

constraint

is the type of constraint. The following is a list of valid values:

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

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

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

CHECK (WHERE-expression): limits the data values of variables to a specific set, range, or list of values. This behavior is accomplished with a WHERE expression.

PRIMARY KEY (variables): specifies a primary key, that is, a set of variables that do not contain missing values and whose values are unique.

Requirement:When defining overlapping primary key and foreign key constraints, which means that variables in a data file are part of both a primary key and a foreign key definition, if you use exactly the same variables, then the variables must be defined in a different order.

Interaction:A primary key affects the values of an individual data file until it has a foreign key referencing it.

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 variables in another data file. The referential actions are enforced when updates are made to the values of a primary key variable that is referenced by a foreign key.

There are three types of referential actions: RESTRICT, SET NULL, and CASCADE:

The following operations can be done with the RESTRICT referential action:

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

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

The following operations can be done with the 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.

The following operations can be done with the CASCADE referential action:

an update operation: modifies the primary key value, and additionally modifies any matching foreign key values to the same value. CASCADE is not supported for delete operations.

Default:RESTRICT is the default action if no referential action is specified.

Requirements:When defining overlapping primary key and foreign key constraints, which means that variables in a data file are part of both a primary key and a foreign key definition, the following actions are required:

If you use exactly the same variables, then the variables must be defined in a different order.

The foreign key's update and delete referential actions must both be RESTRICT.

Interaction:Before it enforces a SET NULL or CASCADE referential action, SAS checks to see whether there are other foreign keys that reference the primary key and that specify RESTRICT for the intended operation. If RESTRICT is specified, or if the constraint reverts to the default values, then RESTRICT is enforced for all foreign keys, unless no foreign key values match the values to updated or deleted.

Optional Arguments

<constraint-name=>

is an optional 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

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.

<MESSAGE='message-string' <MSGTYPE=USER>>

message-string is the text of an error message to be written to the log when the data fails the constraint:

ic create not null(socsec)    
   message='Invalid Social Security number';

<MSGTYPE=USER> controls the format of the integrity constraint error message. By default when the MESSAGE= option is specified, the message that you define is inserted into the SAS error message for the constraint, separated by a space. MSGTYPE=USER suppresses the SAS portion of the message.

Length:The maximum length of the message is 250 characters.

Example: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 b);
ic create forkey = foreign key (a b) references table-name
  on update cascade on delete set null;
ic create not null (x);