Creating a Table That Has Integrity Constraints

Overview

Integrity constraints are rules that you can specify in order to restrict the data values that can be stored for a column in a table. SAS enforces integrity constraints when values associated with a column are added, updated, or deleted. Integrity constraints help you preserve the validity and consistency of your data.
You can create integrity constraints by using either PROC SQL or PROC DATASETS. PROC DATASETS can assign constraints only to an existing table. PROC SQL can assign constraints either as it creates a new table or as it modifies an existing table. This chapter discusses the use of PROC SQL to Create integrity constraints while creating a table.
Note: To learn more about the use of PROC DATASETS to Create integrity constraints, see Modifying SAS Data Sets and Tracking Changes. For additional information about integrity constraints, see the SAS documentation.
Note: To add integrity constraints to an existing table using PROC SQL, use the ALTER TABLE statement.
When you place integrity constraints on a table, you specify the type of constraint that you want to create. Each constraint has a different action.
Constraint Type
Action
CHECK
Ensures that a specific set or range of values are the only values in a column. It can also check the validity of a value in one column based on a value in another column within the same row.
NOT NULL
Guarantees that a column has nonmissing values in each row.
UNIQUE
Enforces uniqueness for the values of a column.
PRIMARY KEY
Uniquely defines a row within a table, which can be a single column or a set of columns. A table can have only one PRIMARY KEY. The PRIMARY KEY includes the attributes of the constraints NOT NULL and UNIQUE.
FOREIGN KEY
Links one or more rows in a table to a specific row in another table by matching a column or set of columns (a FOREIGN KEY) in one table with the PRIMARY KEY in another table. This parent/child relationship limits modifications made to both PRIMARY KEY and FOREIGN KEY constraints. The only acceptable values for a FOREIGN KEY are values of the PRIMARY KEY or missing values.
Note: When you add an integrity constraint to a table that contains data, SAS checks all data values to determine whether they satisfy the constraint before the constraint is added.
You can use integrity constraints in two ways, general and referential. General constraints enable you to restrict the data values accepted for a column in a single table. Referential constraints enable you to link the data values of a column in one table to the data values of columns in another table.

General Integrity Constraints

General integrity constraints enable you to restrict the values of columns within a single table. The following four integrity constraints can be used as general integrity constraints:
  • CHECK
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY.
Note: A PRIMARY KEY constraint is a general integrity constraint if it does not have any FOREIGN KEY constraints referencing it. A PRIMARY KEY used as a general constraint is a shortcut for assigning the constraints NOT NULL and UNIQUE.

Referential Integrity Constraints

A referential integrity constraint is created when a PRIMARY KEY integrity constraint in one table is referenced by a FOREIGN KEY integrity constraint in another table. There are two steps that must be followed to create a referential integrity constraint:
  1. Define a PRIMARY KEY constraint on the first table.
  2. Define a FOREIGN KEY constraint on other tables.
Note: Integrity constraints
  • follow ANSI standards
  • cannot be defined for views
  • cannot be defined for historical versions of generation data sets.
To create a table that has integrity constraints, use a CREATE TABLE statement that specifies both columns and constraints. There are two ways to specify integrity constraints in the CREATE TABLE statement:
  • in a column specification
  • as a separate constraint specification.
You can use either or both of these methods in the same CREATE TABLE statement.

Creating a Constraint in a Column Specification

Earlier in this chapter, you learned how to create a table by using a CREATE TABLE statement that contains column specifications:
CREATE TABLE table-name
(column-specification-1<,
...column-specification-n>);
You also learned that a column specification consists of these elements:
column-definition <column-constraint-1<, ... column-constraint-n>>
<MESSAGE='message-string' <MSGTYPE=message-type>>
The column specifications used in earlier examples contained only the column definition. Now we learn how to create an integrity constraint with a column, by specifying the optional column constraint in the column specification:
General form, column-constraint in a column-specification:
column-definition <column-constraint-1<, ... column-constraint-n>>
<MESSAGE='message-string' <MSGTYPE=message-type>>
Here is an explanation of the syntax:
column-constraint
is one of the following:
CHECK (expression)
specifies that all rows in the table (which is specified in the CREATE TABLE statement) satisfy the expression, which can be any valid where-expression.
DISTINCT
specifies that the values of the column must be unique within the table. This constraint is identical to UNIQUE.
NOT NULL
specifies that the column does not contain a null or missing value, including special missing values.
PRIMARY KEY
specifies that the column is a PRIMARY KEY column, that is, a column that does not contain missing values and whose values are unique.
REFERENCES table-name <ON DELETE referential-action> <ON UPDATE referential-action>
specifies that the column is a FOREIGN KEY, that is, a column whose values are linked to the values of the PRIMARY KEY column in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a PRIMARY KEY column that is referenced by the FOREIGN KEY are updated or deleted. The referential-action specifies the type of action to be performed on all matching FOREIGN KEY values and is one of the following:
CASCADE
allows PRIMARY KEY data values to be updated, and updates matching values in the FOREIGN KEY to the same values.
Note: This referential action is currently supported for updates only.
RESTRICT
occurs only if there are matching FOREIGN KEY values. This referential action is the default.
SET NULL
sets all matching FOREIGN KEY values to NULL.
UNIQUE
specifies that the values of the column must be unique within the table. This constraint is identical to DISTINCT.
Note: The optional MSGTYPE= and MESSAGE= elements are discussed later in this chapter.
Just like a column, an integrity constraint must have a unique name within the table. If you create an integrity constraint by specifying a column constraint in a column specification, then SAS automatically assigns a name to the constraint. The form of the constraint name depends on the type of constraint, as shown below:
Constraint Type
Default Name
CHECK
_CKxxxx_
FOREIGN KEY
_FKxxxx_
NOT NULL
_NMxxxx_
PRIMARY KEY
_PKxxxx_
UNIQUE
_UNxxxx_
Note: xxxx is a counter that begins at 0001.
Here is an example of a PROC SQL step that creates integrity constraints by specifying one or more column constraints in a column specification.

Example

Suppose you need to create the table Work.Employees to store the identification number, name, gender, and hire date for all employees. In addition, you want to ensure the following:
  • the ID column contains only values that are nonmissing and unique
  • the Gender column contains only the values M and F.
The following PROC SQL step creates the table Work.Employees, that contains four columns and integrity constraints for two of those columns: 
proc sql;
   create table work.employees
      (ID char (5) primary key,
      Name char(10),
      Gender char(1) not null check(gender in ('M','F')),
      HDate date label='Hire Date'),
In the column specification for ID, the PRIMARY KEY column constraint ensures that the ID column contains only values that are nonmissing and unique.
The column specification for Gender defines two integrity constraints:
  • The NOT NULL column constraint ensures that the values of Gender are nonmissing values.
  • The CHECK column constraint ensures that the values of Gender satisfy the expression gender in ('M','F').
Here is another method of creating integrity constraints: specifying a constraint specification in the CREATE TABLE statement.

Creating a Constraint By Using a Constraint Specification

Sometimes you might prefer to Create integrity constraints outside of column specifications, by specifying individual constraint specifications in the CREATE TABLE statement:
CREATE TABLE table-name
(column-specification-1<,
...column-specification-n><,
constraint-specification-1><,
...constraint-specification-n>);
The first specification in the CREATE TABLE statement must be a column specification. However, following the initial column specification in the statement, you can include multiple additional column specifications, constraint specifications, or both. All specifications after the first column specification can be listed in any order. The entire list of column specifications and constraint specifications follows the same guidelines that were presented earlier for column specifications:
  • The entire set of column specifications and constraint specifications must be enclosed in parentheses.
  • Multiple column specifications and constraint specifications must be separated by commas.
There are several important differences between specifying an integrity constraint within a column specification and specifying an integrity constraint by using a separate constraint specification. Using a constraint specification offers the following advantages:
  • You can specify a name for the constraint. In fact, you must specify a name, because SAS does not automatically assign one.
  • For certain constraint types, you can define a constraint for multiple columns in a single specification.
The syntax of a constraint specification is shown below.
CONSTRAINT constraint-name constraint <MESSAGE='message-string'
<MSGTYPE=message-type>>
constraint-name
specifies a name for the constraint that is being specified. The name must be a valid SAS name.
CAUTION:
PRIMARY, FOREIGN, MESSAGE, UNIQUE, DISTINCT, CHECK, and NOT cannot be used as values for constraint-name.
constraint
is one of the following:
CHECK (expression)
specifies that all rows in table-name (which is specified in the CREATE TABLE statement) satisfy the expression, which can be any valid where-expression.
DISTINCT (column-1<, ... column-n>)
specifies that the values of each column must be unique within the table. This constraint is identical to UNIQUE.
FOREIGN KEY (column-1<, ... column-n>)
REFERENCES table-name
<ON DELETE referential-action>
<ON UPDATE referential-action>
specifies a FOREIGN KEY, that is, a set of columns whose values are linked to the values of the PRIMARY KEY column in another table (the table name that is specified for REFERENCES). The referential-actions are performed when the values of a PRIMARY KEY column that is referenced by the FOREIGN KEY are updated or deleted. The referential-action specifies the type of action to be performed on all matching FOREIGN KEY values, and is one of the following:
  • CASCADE
    allows PRIMARY KEY data values to be updated, and updates matching values in the FOREIGN KEY to the same values.
    Note: This referential action is currently supported for updates only.
  • RESTRICT
    occurs only if there are matching FOREIGN KEY values. This referential action is the default.
  • SET NULL
    sets all matching FOREIGN KEY values to NULL.
NOT NULL (column)
specifies that the column does not contain a null or missing value, including special missing values.
PRIMARY KEY (column-1<, ... column-n>)
specifies one or more columns as PRIMARY KEY columns, that is, columns that do not contain missing values and whose values are unique.
UNIQUE (column-1<, ... column-n>)
specifies that the values of each column must be unique within the table. This constraint is identical to DISTINCT.
MESSAGE=
specifies a message-string that specifies the text of an error message that is written to the SAS log when the integrity constraint is not met. The maximum length of message-string is 250 characters.
MSGTYPE=
specifies the message-type, which specifies how the error message is displayed in the SAS log when an integrity constraint is not met. The message-type is one of the following:
NEWLINE the text that is specified for MESSAGE= is displayed in addition to the default error message for that integrity constraint.
USER only the text that is specified for MESSAGE= is displayed.
Note: Elements within a constraint-specification must be separated by spaces.
You might have noticed another difference between the two methods of creating an integrity constraint. When you use a column specification to create a FOREIGN KEY integrity constraint, you use the keyword FOREIGN KEY in addition to the keyword REFERENCES.
Here is an example of a PROC SQL step that uses column specifications to Create integrity constraints on a column.

Example

In an example earlier in this chapter, the table Work.Discount was created to hold data about discounts that are offered by an airline. Suppose you now want to ensure that the table
  • holds only discounts that are less than or equal to .5
  • does not allow missing values for Destination.
Create a new version of the table Work.Discount, called Work.Discount3, that includes two integrity constraints. One integrity constraint limits the values that can be entered in the Discount column and the other prevents missing values from being entered in the Destination column. The following PROC SQL step creates Work.Discount3 by specifying four columns and two integrity constraints: 
proc sql;
   create table work.discount3 
          (Destination char(3),
          BeginDate num Format=date9.,
          EndDate num format=date9.,
          Discount num,
          constraint ok_discount check (discount le .5),
          constraint notnull_dest not null(destination));
The CHECK integrity constraint named OK_Discount uses the WHERE expression discount le .5 to limit the values that can be added to the Discount column.
The NOT NULL integrity constraint named NotNull_Dest prevents missing values from being entered in Destination.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.149.28.126