Alters an existing table.

Syntax

---

ALTER TABLE <Name>
[ADD [COLUMN] <ColumnName> <ColumnDefinition>]
[ALTER [COLUMN] <ColumnName> <ColumnAlterOptions>|AS <ColumnDefinition>
[RENAME [COLUMN] <ColumnName> TO <ColumnName>]
[DROP [COLUMN] <ColumnName>]
[ADD [CONSTRAINT <ConstraintName>] <ConstraintDefinition>]
[ALTER CONSTRAINT <ConstraintName> <ConstraintAlterOptions>|AS <ConstraintDefinition>
[RENAME CONSTRAINT <ConstraintName> TO <ConstraintName>
[DROP CONSTRAINT <ConstraintName>]
[,ADD|ALTER|DROP]
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[READONLY|READWRITE]
[ENCRYPTED|UNENCRYPTED]
[INDEX PAGE SIZE <IndexPageSize>]
[BLOB BLOCK SIZE <BLOBBlockSize>]
[PUBLISH BLOCK SIZE <PublishBlockSize>]
[PUBLISH COMPREESSION <Compression>]
[MAX ROW BUFFER SIZE <MaxRowBufferSize>]
[MAX INDEX BUFFER SIZE <MaxIndexBufferSize>]
[MAX BLOB BUFFER SIZE <MaxBLOBBufferSize>]
[MAX PUBLISH BUFFER SIZE <MaxPublishBufferSize>]
[ATTRIBUTES <CustomAttributes>]
[NO BACKUP FILES]

<ColumnDefinition> = 

<DataType>
[COMPRESSION <Compression>]
[GENERATED <GenerationOptions>|COMPUTED <ComputationOptions>|
DEFAULT <DefaultExpression>]
[<ColumnConstraintDefinition>] [<ColumnConstraintDefinition>]
[NOT NULL [ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]]
[<ColumnConstraintDefinition>] [<ColumnConstraintDefinition>]
[DESCRIPTION <Description>]
[AT <ColumnPos>]

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

<DataType> =

CHARACTER|CHAR [(<Length>]) [<CollationName>]
CHARACTER VARYING|VARCHAR [(<Length>]) [<CollationName>]
GUID
BYTE [(<LengthInBytes>])
BYTE VARYING|VARBYTE [(<LengthInBytes>])
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB [<CollationName>]
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT [(<Precision>,<Scale>)]
DECIMAL|NUMERIC [(<Precision>,<Scale>)]
DATE
TIME
TIMESTAMP
INTERVAL YEAR [TO MONTH]
INTERVAL MONTH
INTERVAL DAY [TO HOUR|MINUTE|SECOND|MSECOND]
INTERVAL HOUR [TO MINUTE|SECOND|MSECOND]
INTERVAL MINUTE [TO SECOND|MSECOND]
INTERVAL SECOND [TO MSECOND]
INTERVAL MSECOND

<Compression> = 0..9

<GenerationOptions> =
ALWAYS AS <GenerateExpression>|
ALWAYS AS IDENTITY (START WITH <SeedValue>, INCREMENT BY <IncrementValue>)|
BY DEFAULT AS IDENTITY (START WITH <SeedValue>, INCREMENT BY <IncrementValue>)

<GenerateExpression> =
Any valid SQL expression that does not include any sub-queries

<ComputationOptions> =
ALWAYS AS <ComputeExpression>

<ComputeExpression> =
Any valid SQL expression that does not include any sub-queries
or user-defined function references

<DefaultExpression> =
Any valid SQL expression that does not include any sub-queries

<ColumnConstraintDefinition> =

CHECK <CheckExpression>
PRIMARY KEY|
UNIQUE|
REFERENCES <TableName> [(<ColumnName> [,<ColumnName>])]
   [ON UPDATE RESTRICT|ON DELETE RESTRICT]
[ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]

<CheckExpression> =
Any valid SQL expression that does not include any sub-queries

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

<ColumnAlterOptions> =

[SET <DefaultExpression>|DROP DEFAULT|DESCRIPTION <Description>|
MOVE TO <ColumnPos>|RESTART WITH <SeedValue>]

<ConstraintDefinition> = 

CHECK <CheckExpression>
PRIMARY KEY (<ColumnName> [,<ColumnName>])|
UNIQUE (<ColumnName> [,<ColumnName>])|
FOREIGN KEY (<ColumnName> [,<ColumnName>])
   REFERENCES <TableName> [(<ColumnName> [,<ColumnName>])]
   [ON UPDATE RESTRICT|ON DELETE RESTRICT]
[ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]
[DESCRIPTION <Description>]

<CheckExpression> =
Any valid SQL expression that does not include any sub-queries

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

<ConstraintAlterOptions> =

[DESCRIPTION <Description>]

Usage

---

Use this statement to alter the structure of an existing table. You may add new columns or constraints, alter existing columns, or drop existing columns or constraints.

To alter an existing column in a table, use the ALTER COLUMN AS clause. To alter an existing constraint in a table, use the ALTER CONSTRAINT AS clause. These clauses allow for the complete re-definition of a column or constraint.


The AT clause is 1-based, with 1 being the first column and the column count being the last column.


The NO BACKUP FILES clause is optional. Unless this clause is specified, ElevateDB will create backup files (*.old) of any physical table files that were altered during the execution of the statement. Also, this clause does not apply to physical backup files created for the database catalog, which are always created and retained.

Examples

---

-- The following statement alters the structure of the
-- Customer table by adding a new Notes column.

ALTER TABLE "Customer"
ADD COLUMN Notes CLOB

-- The following statement alters the structure of the
-- Customer table by adding a new foreign key constraint
-- on the State column that establishes a referential
-- integrity link to the State table.

ALTER TABLE "Customer"
ADD CONSTRAINT "State_ForeignKey" FOREIGN KEY REFERENCES "State"

-- The following statement alters the structure of the
-- Customer table by renaming the State column to StateProvince.

ALTER TABLE "Customer"
RENAME COLUMN State TO StateProvince

Required Privileges

---

The current user must be granted the ALTER privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: