Creating and Updating Tables and Views

Creating and Using Integrity Constraints in a Table

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

nonmissing
unique
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. [cautionend]

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.

Top of Page