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 via an engine that does not support UPDATE processing.

Featured in: Updating Data in a PROC SQL Table

ALTER TABLE table-name
<constraint-clause> <, constraint-clause>...>;

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

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

<DROP column <,column>...>;

where each constraint-clause is one of the following:

ADD <CONSTRAINT constraint-name> constraint

DROP CONSTRAINT constraint-name

DROP FOREIGN KEY constraint-name [Note: This is a DB2 extension.]

DROP PRIMARY KEY [Note: This is a DB2 extension.]

where constraint can be one of the following:

NOT NULL (column)

CHECK (WHERE-clause)

PRIMARY KEY (columns)

DISTINCT (columns)

UNIQUE (columns)

FOREIGN KEY (columns)
REFERENCES table-name
<ON DELETE referential-action > <ON UPDATE referential-action>

Arguments

column

names a column in table-name.

column-definition

See column-definition .

constraint-name

specifies the name for the constraint being specified.

referential-action

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

RESTRICT: occurs only if there are matching foreign key values. This is the default referential action.
SET NULL: sets all matching foreign key values to NULL.

table-name

refers to the name of table containing the primary key referenced by the foreign key.

WHERE-clause

specifies a SAS 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(s).

Changing Column Attributes
If a column is already in the table, you can change the following column attributes 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
To change a column's name, you must use the SAS data set option RENAME=. You cannot change this attribute with the ALTER TABLE statement. RENAME= is described in 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, all the indexes (simple and composite) in which the column participates are also dropped. See CREATE INDEX Statement for more information on 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.

Chapter Contents
Previous
Next
Top of Page

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 via an engine that does not support UPDATE processing.
Featured in:	Updating Data in a PROC SQL Table