Understanding Integrity Constraints

Overview

Now that you know how to modify data in place, you might be wondering how you can protect or ensure the integrity of your data when it is modified. Integrity constraints are rules that you can specify in order to restrict the data values that can be stored for a variable in a data set. SAS enforces integrity constraints when values that are associated with a variable are added, updated, or deleted using techniques that modify data in place, such as the following:
  • a DATA step with the MODIFY statement
  • an interactive data editing window
  • PROC SQL with the INSERT INTO, SET, or UPDATE statements
  • PROC APPEND
When you add an integrity constraint to the table that contains data, SAS checks all data values to determine whether they satisfy the constraint before the constraint is added.
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 constraint includes the attributes of the NOT NULL and UNIQUE constraints.
FOREIGN KEY
specifies variables whose values are linked to the values of the primary key variables in another data set. This parent/child relationship limits modifications that are made to both tables.
Note: When you place integrity constraints on a SAS data set, you specify the type of constraint that you want to create. Each constraint has a different action.
You can use integrity constraints in two ways, general and referential. General constraints operate within a data set, and referential constraints operate between data sets.

General Integrity Constraints

General integrity constraints enable you to restrict the values of variables within a single data set. The following 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 as long as it does not have any FOREIGN KEY constraints referencing it. When PRIMARY KEY is used as a general constraint, it is simply a shortcut for assigning the NOT NULL and UNIQUE constraints.

Referential Integrity Constraints

Referential constraints enable you to link the data values of a column in one data set to the data values of columns in another data set. You create a referential integrity constraint when a FOREIGN KEY integrity constraint in one data set references a PRIMARY KEY integrity constraint in another data set. To create a referential integrity constraint, you must do the following:
  1. Define a PRIMARY KEY constraint on the first data set.
  2. Define a FOREIGN KEY constraint on other data sets.
..................Content has been hidden....................

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