ALTER TABLE Statement

Adds or drops table columns and modifies column definitions.

Category: Data Definition
Restriction: Constraint syntax is defined by the data source. The constraint syntax that is provided here is a general syntax. For complete constraint syntax, see the documentation for your data source. The SPD Engine data source does not support constraints.
Supports: EXECUTE Statement
Data source: SAS data set, Aster, DB2 under UNIX and PC, Greenplum, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Note: Braces in the syntax convention indicate a syntax grouping. The escape character ( \ ) before a brace indicates that the brace is required in the syntax. Table options must be contained by braces ( { } ).
Syntax
Arguments
<column-definition> Arguments
<column-constraint> and <table-constraint> Arguments
Details
Adding and Deleting Columns
Adding and Deleting Constraints
Altering Column Definitions
Comparisons
See Also

Syntax

ALTER TABLE table [\{OPTIONS SAS-table-option=value [, ...SAS-table-option=value] \}]
ADD COLUMN <column-definition> [, ...<column-definition>]
| ADD CONSTRAINT <table-constraint>
| ALTER [COLUMN] <column-definition> [, ...<column-definition> ]
| DROP COLUMN column [, ...column] [FORCE]
| DROP CONSTRAINT constraint [, ...constraint]
| RENAME TO table
| RENAME column TO column
;
<column-definition>::=
column data-type [<column-constraint>] [ SET DEFAULT value | DROP DEFAULT ]
<column-constraint>::=
CONSTRAINT constraint
{ CHECK (search-condition)
| PRIMARY KEY
| UNIQUE
| NOT NULL }
<table-constraint>::=
CONSTRAINT constraint
{ CHECK (search-condition)
| PRIMARY KEY (column [, …column])
| UNIQUE (column [, ...column])
| <referential-constraint> }
[<constraint-check-time>]
<referential-constraint>::=
FOREIGN KEY (referencing-column [, ... referencing-column])
REFERENCES referenced-table (referenced-column [, ...referenced-column])
[<referential-trigger-action>]
<referential-trigger-action>::=
{ [ON UPDATE <referential-action> [ON DELETE <referential-action>]] }
| { [ON DELETE <referential-action> [ON UPDATE <referential-action>]] }
<referential-action>::=
CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION
<constraint-check-time>::=
{ DEFERRABLE [INITIALLY DEFERRED | INITIALLY IMMEDIATE] }
| { [INITIALLY DEFERRED | INITIALLY IMMEDIATE] DEFERRABLE }
| INITIALLY DEFERRED
| { NOT DEFERRABLE [INITIALLY IMMEDIATE] }
| { [INITIALLY IMMEDIATE] NOT DEFERRABLE }

Arguments

table

specifies a table to modify. table can be specified in one of these forms:

  • catalog.schema.table-name
  • schema.table-name
  • catalog.table-name
  • table-name

catalog

is an implementation of the ANSI SQL standard for an SQL catalog. The catalog is a data container object that groups logically related schemas. It is the first-level (top) grouping mechanism in a data organization hierarchy that is used along with a schema to provide a means of qualifying names. A catalog is a metadata object in a SAS Metadata Repository.

schema

is an implementation of the ANSI SQL standard for an SQL schema. The schema is a data container object that groups files such as tables and views and other objects that are supported by a data source such as stored procedures. The schema provides a grouping object that is used along with a catalog to provide a means of qualifying names.

table-name

is the name of the table.

Restriction You cannot alter an MDS table while it is referenced in another transaction or statement. If a request fails, make sure other users are no longer using the table or have disconnected.
Requirement Table naming conventions are based on the data source. When more than one data source is involved, the maximum length of a table name is determined by the maximum length that is supported by all of the data sources and FedSQL. For example, if your data sources are a SAS data set that has a maximum of 32 characters and MySQL that has a maximum of 64 characters, the maximum length of a table name is 32 characters. For more information, see the documentation for your data source.

{OPTIONS SAS-table-option=value [, ...SAS-table-option=value ]}

specifies one or more table options and their respective values to apply to the table.

Requirement The OPTION argument and all table options must be enclosed in braces ( { } ).
See FedSQL Statement Table Options

column

specifies the name of a column in a table.

Requirement Each column in a table must be unique.

ADD COLUMN <column-definition> [, ...<column-definition>]

specifies to add a column to a table.

Data source Aster, DB2 under UNIX and PC, Greenplum, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
See column and <column-definition> Arguments in the CREATE TABLE statement

ADD CONSTRAINT <table-constraint>

specifies to add an integrity constraint to one or more columns.

Data source SAS data set, Aster, DB2 under UNIX and PC, Greenplum, MySQL, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Note Constraint validation and enforcement is performed by the data source. You should be familiar with data source’s requirements before adding or dropping an integrity constraint.
See <column-constraint> and <table-constraint> Arguments in the CREATE TABLE statement

ALTER [COLUMN] <column-definition> [, ...<column-definition>]

specifies to modify the definition of one or more columns.

Data source Aster, DB2 under UNIX and PC, Greenplum, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, Sybase IQ, Teradata
See <column-definition> Arguments in the CREATE TABLE statement

DROP COLUMN column [, ...column] [FORCE]

specifies to delete the specified column from the table. When FORCE is specified, the column is dropped from the table without error processing. Use the FORCE keyword only when you are certain that dropping the column without error processing will not negatively affect the table.

Data source Aster, DB2 under UNIX and PC, Greenplum, Hive, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
See column

DROP CONSTRAINT constraint [, …constraint]

specifies to delete an integrity constraint.

constraint

specifies the name of the constraint to drop.

Data source SAS data set, Aster, DB2 under UNIX and PC, Greenplum, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

RENAME

changes the specified table or column name to a new name. The new name follows the TO keyword.

Data source Aster, DB2 under UNIX and PC, Greenplum, MDS, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata

<column-definition> Arguments

See <column-definition> Arguments in the CREATE TABLE statement.

<column-constraint> and <table-constraint> Arguments

See <column-constraint> and <table-constraint> Arguments in the CREATE TABLE statement.

Details

Adding and Deleting Columns

To add a column to a table, use the ALTER TABLE statement with the ADD COLUMN clause. By using the ADD COLUMN clause, you name the column and define the column attributes, such as the column data type, a default value, and a label for the column. 
alter table personal_info add column em_num char(16);
To delete a column, you need to specify only the column name. 
alter table personal_info drop em_num;

Adding and Deleting Constraints

You can define a constraint on a column that is added with the ADD COLUMN clause in all data sources that support integrity constraints as follows: 
alter table mytest add y char(5) constraint c2 unique;
An exception is SAP HANA. For SAP HANA, you must add the column first, then add the constraint with ADD CONSTRAINT.
By using the ADD CONSTRAINT clause, you can add a constraint for a table, or you can add or modify a constraint for one or more existing columns. The following code adds a primary key constraint to a table with ALTER TABLE.
alter table customers add constraint pkey primary key(custid);
The following code adds a CHECK constraint to an existing column. 
alter table mytest add constraint chk check (col1 < 5);
You delete a constraint by using the DROP CONSTRAINT clause. The following code is an example of deleting a constraint from a SAS data set. The code deletes a primary key constraint that was defined at table creation. When they are defined at table creation, primary keys in a SAS data set are created with the default name _pk0001_. 
alter table customers drop constraint _pk0001_;
When you add or modify constraints, you must know the constraint syntax that the data source supports. Constraint validation is performed by the data source.

Altering Column Definitions

To alter a column definition, use the ALTER TABLE statement with the ALTER clause. 
alter table personal_info alter country set default 'UK';
This example drops a default value from a column.
alter table personal_info alter country drop default;
This example renames a column.
alter table sales rename column y to customers;

Comparisons

You use the ALTER TABLE statement to alter a table after it has been created. Use the CREATE TABLE statement to create a table.

See Also

How to Store, Change, Delete, and Use Stored Formats
Statements:
CREATE TABLE Statement
DROP INDEX Statement
Copyright © SAS Institute Inc. All rights reserved.