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