Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

ALTER SYNCHRONIZATION USER statement [MobiLink] Next Page

ALTER TABLE statement


Use this statement to modify a table definition, disable dependent views, or enable a table to take part in Replication Server replication.

Syntax

ALTER TABLE [owner.]table-name { alter-clause, ... }

alter-clause :
ADD create-clause
| ALTER column-name column-alteration
| ALTER [ CONSTRAINT constraint-name ] CHECK ( condition )
| DROP drop-object
| RENAME rename-object
| table-alteration

create-clause :
column-name [ AS ] column-data-type [ new-column-attribute ... ]
| table-constraint
| PCTFREE integer

column-alteration :
{ column-data-type | alterable-column-attribute } [ alterable-column-attribute ... ]
| SET COMPUTE ( compute-expression )
| ADD [ constraint-name ] CHECK ( condition )
| DROP { DEFAULT | COMPUTE | CHECK | CONSTRAINT constraint-name }

drop-object :
column-name
| CHECK
| CONSTRAINT constraint-name
| UNIQUE [ CLUSTERED ] ( index-columns-list )
| FOREIGN KEY fkey-name
| PRIMARY KEY

rename-object :
new-table-name
| column-name TO new-column-name
| CONSTRAINT constraint-name TO new-constraint-name

table-alteration :
PCTFREE DEFAULT
| REPLICATE { ON | OFF }
| [ NOT ] ENCRYPTED

new-column-attribute :
NULL
| DEFAULT default-value
| COMPRESSED
| INLINE { inline-length | USE DEFAULT }
| PREFIX { prefix-length | USE DEFAULT }
| [ NO ] INDEX
| IDENTITY
| COMPUTE ( expression )
| column-constraint

table-constraint :
[ CONSTRAINT constraint-name ] {
CHECK ( condition )
| UNIQUE [ CLUSTERED | NONCLUSTERED ] ( column-name [ ASC | DESC ], ... )
| PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] ( column-name [ ASC | DESC ], ... )
| foreign-key
}

column-constraint :
[ CONSTRAINT constraint-name ] {
CHECK ( condition )
| UNIQUE [ CLUSTERED | NONCLUSTERED ] [ ASC | DESC ]
| PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] [ ASC | DESC ]
| REFERENCES table-name [ ( column-name ) ]
[ MATCH [ UNIQUE] { SIMPLE | FULL } ]
[ actions ][ CLUSTERED | NONCLUSTERED ]
| NOT NULL
}

alterable-column-attribute :
[ NOT ] NULL
| DEFAULT default-value
| [ CONSTRAINT constraint-name ] CHECK { NULL | ( condition ) }
| [ NOT ] COMPRESSED
| INLINE { inline-length | USE DEFAULT }
| PREFIX { prefix-length | USE DEFAULT }
| [ NO ] INDEX

default-value :
special-value
| string
| global variable
| [ - ] number
| ( constant-expression )
| built-in-function ( constant-expression )
| AUTOINCREMENT
| GLOBAL AUTOINCREMENT [ ( partition-size ) ]
| NULL
| TIMESTAMP
| UTC TIMESTAMP
| LAST USER
| USER

special-value :
CURRENT {
DATABASE

| DATE
| REMOTE USER
| TIME
| TIMESTAMP
| UTC TIMESTAMP
| USER
| PUBLISHER }

foreign-key :
[ NOT NULL ] FOREIGN KEY [ role-name ]
[ ( column-name [ ASC | DESC ], ... )
REFERENCES table-name
[ ( pkey-column-list ) ]
[ MATCH [ UNIQUE] { SIMPLE | FULL } ]
[ actions ] [ CHECK ON COMMIT ] [ CLUSTERED ]
[ FOR OLAP WORKLOAD ]

actions :
[ ON UPDATE action ] [ ON DELETE action ]

action :
CASCADE | SET NULL | SET DEFAULT | RESTRICT

Syntax 2 - Disabling view dependencies

ALTER TABLE [owner.]table-name {
DISABLE VIEW DEPENDENCIES
}

Parameters

ADD column-name [ AS ] column-data-type [ new-column-attribute ... ] clause    Use this syntax to add a new column to the table, specifying the data type and attributes for the column. For more information about what data type to specify, see SQL Data Types.

Possible column attributes include:

ADD table-constraint clause    Use this clause to add a table constraint. Table constraints place limits on what columns in the table can hold. When adding or altering table constraints, the optional constraint name allows you to modify or drop individual constraints. Following is a list of the table constraints you can add.

ADD PCTFREE clause    Specify the percentage of free space you want to reserve in each table page. The free space is used if rows increase in size when the data is updated. If there is no free space in a table page, every increase in the size of a row on that page requires the row to be split across multiple table pages, causing row fragmentation and possible performance degradation. A free space percentage of 0 specifies that no free space is to be left on each page—each page is to be fully packed. A high free space percentage causes each row to be inserted into a page by itself. If PCTFREE is not set, or is dropped, the default PCTFREE value is applied according to the database page size (200 bytes for a 4 KB, and up, page size). The value for PCTFREE is stored in the ISYSTAB system table. When PCTFREE is set, all subsequent inserts into table pages use the new value, but rows that were already inserted are not affected. The value persists until it is changed. The PCTFREE specification can be used for base, global temporary, or local temporary tables.

ALTER column-name column-alteration clause    Use this clause to change attributes for the specified column. If a column is contained in a unique constraint, a foreign key, or a primary key, then the constraint or key must be deleted before the column can be modified. Following is a list of the alterations you can make. For further information about these attributes, see CREATE TABLE statement.

ALTER CONSTRAINT constraint-name CHECK clause    Use this clause to alter a named check constraint for the table.

DROP clause    Use this clause to drop a column, a table constraint, or an index. Possible objects to drop for tables or columns include:

RENAME clause    Use this clause to rename a table, column or constraint.

table-alteration clause    Use this clause to alter attributes for the table.

DISABLE VIEW DEPENDENCIES clause    Use this clause to disable any non-materialized views that are dependent on the table. To re-enable the views disabled by this clause, you must execute an ALTER VIEW ... ENABLE statement for each view. This clause does not disable dependent materialized views; you must disable them using the ALTER MATERIALIZED VIEW ... DISABLE statement. Consequently, this clause cannot be used on a table while it has valid dependent materialized views. See ALTER MATERIALIZED VIEW statement.

Remarks

The ALTER TABLE statement changes table attributes (column definitions, constraints, and so on) in an existing table.

The database server keeps track of object dependencies in the database. Alterations to the schema of a table may impact dependent views. Also, if there are materialized views that are dependent on the table you are attempting to alter, you must first disable them using the ALTER MATERIALIZED VIEW ... DISABLE statement. For information on view dependencies, see View dependencies.

You cannot use ALTER TABLE on a local temporary table.

ALTER TABLE is prevented whenever the statement affects a table that is currently being used by another connection. ALTER TABLE can be time-consuming, and the database server does not process other requests referencing the table while the statement is being processed.

For more information on using the CLUSTERED option, see Using clustered indexes.

Permissions

Must be one of the following:

ALTER TABLE requires exclusive access to the table.

Global temporary tables cannot be altered unless all users that have referenced the temporary table have disconnected.

Cannot be used within a snapshot transaction. See Snapshot isolation.

Side effects

Automatic commit.

A checkpoint is carried out at the beginning of the ALTER TABLE operation, and further checkpoints are suspended until the ALTER operation completes.

Once you alter a column or table, any stored procedures, views, or other items that refer to the altered column may no longer work.

If you change the declared length or type of a column, or drop a column, the statistics for that column are dropped. For information on how to generate new statistics, see Updating column statistics.

See also
Standards and compatibility
Example

The following example adds a new column to the Employees table showing which office they work in.

ALTER TABLE Employees
ADD Office CHAR(20) DEFAULT 'Boston';

The following example drops the Office column from the Employees table.

ALTER TABLE Employees
DROP Office;

The Street column in the Customers table can currently hold up to 35 characters. To allow it to hold up to 50 characters, execute the following:

ALTER TABLE Customers
ALTER Street CHAR(50);

The following example adds a column to the Customers table, assigning each customer a sales contact.

ALTER TABLE Customers
ADD SalesContact INTEGER
REFERENCES Employees ( EmployeeID )
ON UPDATE CASCADE
ON DELETE SET NULL;

This foreign key is constructed with cascading updates and is set to NULL on deletes. If an employee has their employee ID changed, the column is updated to reflect this change. If an employee leaves the company and has their employee ID deleted, the column is set to NULL.