Integrity constraints are rules
that you specify to guarantee the accuracy, completeness, or consistency
of data in tables. All integrity constraints are enforced when you
insert, delete, or alter data values in the columns of a table for
which integrity constraints have been defined. Before a constraint
is added to a table that contains existing data, all the data is checked
to determine that it satisfies the constraints.
You
can use general integrity constraints to verify that data in a column
is one of the following:
-
-
-
both nonmissing and unique
-
within a specified set or range
of values
You can also apply referential
integrity constraints to link the values in a specified column (called
a
primary key) of one table to values of
a specified column in another table. When linked to a primary key,
a column in the second table is called a
foreign key.
When you define referential
constraints, you can also choose what action occurs when a value in
the primary key is updated or deleted.
-
You can prevent the primary key
value from being updated or deleted when matching values exist in
the foreign key. This is the default.
-
You can allow updates and deletions
to the primary key values. By default, any affected foreign key values
are changed to missing values. However, you can specify the CASCADE
option to update foreign key values instead. Currently, the CASCADE
option does not apply to deletions.
You can choose separate actions for updates and for
deletions.
Note: Integrity constraints cannot
be defined for views.
The following example
creates integrity constraints for a table, MYSTATES, and another table,
USPOSTAL. The constraints are as follows:
-
state name must be unique and nonmissing
in both tables
-
population must be greater than
0
-
continent must be either North
America or Oceania
proc sql;
create table sql.mystates
(state char(15),
population num,
continent char(15),
/* contraint specifications */
constraint prim_key primary key(state),
constraint population check(population gt 0),
constraint continent check(continent in ('North America', 'Oceania')));
create table sql.uspostal
(name char(15),
code char(2) not null, /* constraint specified as */
/* a column attribute */
constraint for_key foreign key(name) /* links NAME to the */
references sql.mystates /* primary key in MYSTATES */
on delete restrict /* forbids deletions to STATE */
/* unless there is no */
/* matching NAME value */
on update set null); /* allows updates to STATE, */
/* changes matching NAME */
/* values to missing */
The DESCRIBE TABLE statement
displays the integrity constraints in the SAS log as part of the table
description. The DESCRIBE TABLE CONSTRAINTS statement writes only
the constraint specifications to the SAS log.
proc sql;
describe table sql.mystates;
describe table constraints sql.uspostal;
SAS Log Showing Integrity Constraints
NOTE: SQL table SQL.MYSTATES was created like:
create table SQL.MYSTATES( bufsize=8192 )
(
state char(15),
population num,
continent char(15)
);
create unique index state on SQL.MYSTATES(state);
-----Alphabetic List of Integrity Constraints-----
Integrity Where On On
# Constraint Type Variables Clause Reference Delete Update
-------------------------------------------------------------------------------
-49 continent Check continent in
('North
America',
'Oceania')
-48 population Check population>0
-47 prim_key Primary Key state
for_key Referential name SQL. Restrict Set Null
USPOSTAL
NOTE: SQL table SQL.USPOSTAL ( bufsize=8192 ) has the following integrity
constraints:
-----Alphabetic List of Integrity Constraints-----
Integrity On On
# Constraint Type Variables Reference Delete Update
-----------------------------------------------------------------------------
1 _NM0001_ Not Null code
2 for_key Foreign Key name SQL.MYSTATES Restrict Set
Null
Integrity constraints
cannot be used in views. For more information about integrity constraints,
see
SAS Language Reference: Concepts.