SAS Component Language Dictionary

ICCREATE

Creates integrity constraints on a SAS table

Category:

SAS Table

Syntax
Details
Example
See Also

Syntax

sysrc=ICCREATE(table-id,icname,ictype,icvalue<, argument-1, argument-2, argument-3g<, message<, msgtype>>>);

sysrc

contains the status of the operation:

=0	successful
>0	not successful
<0	the operation was completed with a warning

Type: Numeric

table-id

contains the identifier for the SAS table, which is returned by the OPEN function. Integrity constraints can be set for a new SAS table only when it is opened in NEW mode. Integrity constraints can be set for an existing table only when it is opened in UTILITY or NEW mode. (Remember that if you open an existing table in NEW mode, the table is replaced without warning.)

Type: Numeric

icname

is the name of the integrity constraint.

Type: Character

ictype

specifies the type of integrity constraint to create:

'CHECK': specifies that column values must fall within a particular set, range, or list of values, or that values must meet a condition or be the same as values in other columns in the same row.
'FOREIGN': specifies that a column is linked to a column in another SAS table. A foreign key column can contain only a null value or a value that is present in the associated primary key column.
'NOT-NULL': specifies that column values cannot contain missing values.
'PRIMARY': specifies that column values must be unique and that they cannot be missing values. If there is an associated foreign key column, then primary key values cannot be deleted or changed unless the same deletions or changes have been made in values of that foreign key column.
'UNIQUE' | 'DISTINCT': specifies that column values must be unique. If more than one column is specified, then their concatenated values must be unique.

Type: Character

icvalue

specifies the names of one or more columns from the SAS table to which the constraint is applied. Separate multiple column names with blanks. If ictype is CHECK, then icvalue can contain a condition that values must meet.

Type: Character

argument-1, argument-2, argument-3

are additional specifications that are used when ictype is FOREIGN.

argument-1

is the name of the SAS table in which one or more associated primary keys are defined. This links the two SAS tables and creates the primary/foreign key relationship. Use libref.member to specify the SAS table. This argument is required for foreign keys.

argument-2

is the restriction option for update operations on values in foreign key columns:

'RESTRICT': allows the primary key value to be updated only if no foreign key matches the current value to be updated.
'NULL': specifies that when the primary key value is modified, all matching foreign key values are set to NULL.
'CASCADE': specifies that when the primary key value is modified, all matching foreign key values are set to the same value as the primary key.

argument-3

is the restriction option for delete operations in foreign key columns:

'RESTRICT': allows the primary key row to be deleted only if no foreign key values match the deleted value.
'NULL': allows the primary key row to be deleted and sets the values of corresponding foreign keys to null.

Type: Character

message

is your own error message that is displayed when a data value fails to comply with the constraint.

Note: The maximum length for the entire message, including both the user message and the system message that may be appended to the user message, is 256 characters. If the user message is too long, either the user message or the system message will be truncated. [cautionend]

message-type

controls how the user-defined error message is displayed.

'NULL': adds the user-defined message to the beginning of the message that is displayed by SAS and separates the two message with a blank. This behavior is the default.
'USER': replaces the SAS message with the user-defined message.

Details

ICCREATE defines integrity constraints for a SAS table that has been opened in an SCL program. Integrity constraints guarantee the correctness and consistency of data that is stored in a SAS table. Integrity constraints are enforced automatically for each addition, update, or deletion of data in a table that contains a constraint.

You can define integrity constraints for SAS tables that contain zero or more rows. For tables that already contain data, an integrity constraint is compared to values in all table rows. If a single row does not comply with the constraint being defined, then the creation of the constraint fails. When rows are added to tables that have integrity constraints, the new rows are checked against the constraints. If a row violates an integrity constraint, the row is not added to the table. Also, a primary key column cannot be dropped until all foreign key columns that reference the primary key are deleted.

The basic types of integrity constraints are general constraints and referential constraints. The general constraints, which control values in a single SAS table, are CHECK, NOT-NULL, PRIMARY key, and UNIQUE. Referential constraints, which establish a parent-child relationship between columns in two SAS tables, include FOREIGN keys and PRIMARY keys that have one or more FOREIGN key references. A FOREIGN key column (the child) can contain only null values or values that are present in its associated PRIMARY key (the parent). Values for a FOREIGN key cannot be added unless the same values also exist in the associated PRIMARY key.

For more information about integrity constraints, see Preserving the Integrity of Table Data in SCL Programs.

Example

This example creates integrity constraints for the SAS tables MYLIB.ONE and MYLIB.TWO. Although they contain different data, they have one column with shared data, an identifier number that is stored in IDNUM in MYLIB.ONE and in EMPID in MYLIB.TWO. The following icnames are used in the example:

UQ	is a UNIQUE constraint which specifies that the concatenated values of columns D and E must be unique.
WH	is a CHECK constraint which specifies that the sum of the values of columns B and C must be less than 1000.
PK	is a PRIMARY constraint which specifies that the IDNUM column can contain only unique and non-missing values. Because IDNUM is associated with the foreign key column EMPID in the table MYLIB.TWO, values for IDNUM cannot be deleted or changed unless the same changes have been made to the values in the foreign key EMPID.
FK	is a foreign key in the table MYLIB.TWO. EMPID is the foreign key column whose primary key is IDNUM in MYLIB.ONE. Because EMPID is a foreign key column, it can contain only values that are present in IDNUM. The first RESTRICT specifies that a value in IDNUM can be changed only if EMPID does not contain a value that matches the IDNUM value to be changed. The second RESTRICT specifies that a row can be deleted from MYLIB.ONE only if the value of IDNUM does not match a value in EMPID in MYLIB.TWO.
NONULL	is a NOT-NULL constraint which specifies that the EMPID column cannot contain a null value.

The first integrity constraints that are defined for each of the tables MYLIB.ONE and MYLIB.TWO have user-defined messages that will be displayed instead of the SAS message if any data value does not conform to the integrity constraint.

table1=open('mylib.one','V');
rc=iccreate(table1,'uq','Unique','d e','','','',
   'This is my message for uq.','user');
rc=iccreate(table1,'wh','Check','(b + c)< 1000');
rc=iccreate(table1,'pk','Primary','idnum');
rc=close(table1);

table2=open('mylib.two','V');
rc=iccreate(table2,'fk','Foreign','empid','mylib.one',
   'restrict','restrict','This is my message for fk.','user');
rc=iccreate(table2,'nonull','Not-null','empid');
    ...more SCL statements...
rc=close(table1);
rc=close(table2);