Previous Page | Next Page

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

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 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:

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                                
-48 population Check                 population>0                                
-47 prim_key   Primary Key state                                                 
  for_key    Referential name                       SQL.      Restrict Set Null
NOTE: SQL table SQL.USPOSTAL ( bufsize=8192 ) has the following integrity 

               -----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.

Previous Page | Next Page | Top of Page