The SQL Procedure

ALTER TABLE Statement

Adds columns to, drops columns from, and changes column attributes in an existing table. Adds, modifies, and drops integrity constraints from an existing table.

Restriction:	You cannot use any type of view in an ALTER TABLE statement.
Restriction:	You cannot use ALTER TABLE on a table that is accessed by an engine that does not support UPDATE processing.
Restriction:	You must use at least one ADD, DROP, or MODIFY clause in the ALTER TABLE statement.
Featured in:	Updating Data in a PROC SQL Table

ALTER TABLE table-name

;

Arguments

<ADD CONSTRAINT constraint-name constraint-specification<, ... constraint-name constraint-specification>>

adds the integrity constraint that is specified in constraint-specification and assigns constraint-name to it.

<ADD constraint-specification<, ... constraint-specification>>

adds the integrity constraint that is specified in constraint-specification and assigns a default name to it. The default constraint name has the form that is shown in the following table:

Default Name	Constraint Type
_NMxxxx_	Not null
_UNxxxx_	Unique
_CKxxxx_	Check
_PKxxxx_	Primary key
_FKxxxx_	Foreign key

In these default names, xxxx is a counter that begins at 0001.

<ADD column-definition<, ... column-definition>>

adds the column or columns that are specified in each column-definition.

column

names a column in table-name.

column-definition

See column-definition.

constraint

is one of the following integrity constraints:

CHECK (WHERE-clause)

specifies that all rows in table-name satisfy the WHERE-clause.

DISTINCT (column<, ... column>)

specifies that the values of each column must be unique. This constraint is identical to UNIQUE.

FOREIGN KEY (column<, ... column>) REFERENCES table-name <ON DELETE referential-action > <ON UPDATE referential-action>

specifies a foreign key, that is, a set of columns whose values are linked to the values of the primary key variable in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a primary key column that is referenced by the foreign key are updated or deleted.

Restriction:

When defining overlapping primary key and foreign key constraints, the variables in a data file are part of both a primary key and a foreign key definition. The restrictions are as follows:

If you use the exact same variables, then the variables must be defined in a different order.
The foreign key's update and delete referential actions must both be RESTRICT.

NOT NULL (column)

specifies that column does not contain a null or missing value, including special missing values.

PRIMARY KEY (column<, ... column>)

specifies one or more primary key columns, that is, columns that do not contain missing values and whose values are unique.

Restriction:

When you are defining overlapping primary key and foreign key constraints, the variables in a data file are part of both a primary key definition and a foreign key definition. If you use the exact same variables, then the variables must be defined in a different order.

UNIQUE (column<, ... column>)

specifies that the values of each column must be unique. This constraint is identical to DISTINCT.

constraint-name

specifies a name for the constraint that is being specified. The name must be a valid SAS name.

Note: The names PRIMARY, FOREIGN, MESSAGE, UNIQUE, DISTINCT, CHECK, and NOT cannot be used as values for constraint-name. [cautionend]

constraint-specification

consists of

constraint <MESSAGE='message-string' <MSGTYPE=message-type>>

<DROP column<, ... column>>

deletes each column from the table.

<DROP CONSTRAINT constraint-name <, ...constraint-name>>

deletes the integrity constraint that is referenced by each constraint-name. To find the name of an integrity constraint, use the DESCRIBE TABLE CONSTRAINTS clause (see DESCRIBE Statement).

<DROP FOREIGN KEY constraint-name>

Removes the foreign key constraint that is referenced by constraint-name.

Note: The DROP FOREIGN KEY clause is a DB2 extension. [cautionend]

<DROP PRIMARY KEY>

Removes the primary key constraint from table-name.

Note: The DROP PRIMARY KEY clause is a DB2 extension. [cautionend]

message-string

specifies the text of an error message that is written to the log when the integrity constraint is not met. The maximum length of message-string is 250 characters.

message-type

specifies how the error message is displayed in the SAS log when an integrity constraint is not met.

NEWLINE: the text that is specified for MESSAGE= is displayed as well as the default error message for that integrity constraint.
USER: only the text that is specified for MESSAGE= is displayed.

<MODIFY column-definition<, ... column-definition>>

changes one or more attributes of the column that is specified in each column-definition.

referential-action

specifies the type of action to be performed on all matching foreign key values.

CASCADE: allows primary key data values to be updated, and updates matching values in the foreign key to the same values. This referential action is currently supported for updates only.
RESTRICT: prevents the update or deletion of primary key data values if a matching value exists in the foreign key. This referential action is the default.
SET NULL: allows primary key data values to be updated, and sets all matching foreign key values to NULL.

table-name

in the ALTER TABLE statement, refers to the name of the table that is to be altered.
in the REFERENCES clause, refers to the name of table that contains the primary key that is referenced by the foreign key.

table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

WHERE-clause

specifies a SAS WHERE clause. Do not include the WHERE keyword in the WHERE clause.

Specifying Initial Values of New Columns

When the ALTER TABLE statement adds a column to the table, it initializes the column's values to missing in all rows of the table. Use the UPDATE statement to add values to the new column or columns.

Changing Column Attributes

If a column is already in the table, then you can change the following column attributes by using the MODIFY clause: length, informat, format, and label. The values in a table are either truncated or padded with blanks (if character data) as necessary to meet the specified length attribute.

You cannot change a character column to numeric and vice versa. To change a column's data type, drop the column and then add it (and its data) again, or use the DATA step.

Note: You cannot change the length of a numeric column with the ALTER TABLE statement. Use the DATA step instead. [cautionend]

Renaming Columns

You cannot use the RENAME= data set option with the ALTER TABLE statement to change a column's name. However, you can use the RENAME= data set option with the CREATE TABLE or SELECT statement. For more information on the RENAME= data set option, see the section on SAS data set options in SAS Language Reference: Dictionary.

Indexes on Altered Columns

When you alter the attributes of a column and an index has been defined for that column, the values in the altered column continue to have the index defined for them. If you drop a column with the ALTER TABLE statement, then all the indexes (simple and composite) in which the column participates are also dropped. See CREATE INDEX Statement for more information about creating and using indexes.

Integrity Constraints

Use ALTER TABLE to modify integrity constraints for existing tables. Use the CREATE TABLE statement to attach integrity constraints to new tables. For more information on integrity constraints, see the section on SAS files in SAS Language Reference: Concepts.

Top of Page