|The SQL Procedure|
|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
adds the integrity constraint that is specified in constraint-specification and assigns constraint-name to it.
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|
In these default names, xxxx is a counter that begins at 0001.
adds the column or columns that are specified in each column-definition.
names a column in table-name.
is one of the following integrity constraints:
specifies that all rows in table-name satisfy the WHERE-clause.
specifies that the values of each column must be unique. This constraint is identical to UNIQUE.
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.
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:
specifies that column does not contain a null or missing value, including special missing values.
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.|
specifies that the values of each column must be unique. This constraint is identical to DISTINCT.
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.
|constraint <MESSAGE='message-string' <MSGTYPE=message-type>>|
deletes each column from the table.
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).
Removes the foreign key constraint that is referenced by constraint-name.
Note: The DROP FOREIGN KEY clause is a DB2 extension.
Removes the primary key constraint from table-name.
Note: The DROP PRIMARY KEY clause is a DB2 extension.
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.
specifies how the error message is displayed in the SAS log when an integrity constraint is not met.
the text that is specified for MESSAGE= is displayed as well as the default error message for that integrity constraint.
only the text that is specified for MESSAGE= is displayed.
changes one or more attributes of the column that is specified in each column-definition.
specifies the type of action to be performed on all matching foreign key values.
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.
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.
allows primary key data values to be updated, and sets all matching foreign key values to NULL.
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.
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.
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.
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.