Alter table add foreign key sas sql example

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.

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.

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;

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 .