Use the alter table command to change the structure of an existing table. You can:
Add columns and constraints
Change column default values
Add either null or non-null columns
Drop columns and constraints
Change locking scheme
Partition or unpartition tables
Convert column datatypes
Convert the null default value of existing columns
Increase or decrease column length
You can also change a table’s partitioning attributes. See Chapter 10, “Partitioning Tables and Indexes,” for syntax and usage information.
The full syntax of alter table is in the Reference Manual.
alter table includes the following syntax for modifying tables:
alter table table_name
[add column_name datatype [identity | null |
not null] [, column_name datatype [identity
|null | not null]]]
[drop column_name [, column_name]
[modify column_name {[data_type]
[[null] | [not null]]}
[, colum_name datatype [null | not null]]]
Where:
table_name is the table you are altering.
datatype is the datatype of the altered column.
For information about the parameters in the Partitions section and the computed columns section of alter table syntax, see Vol.2, Commands, in the Reference Manual.
You must have the sa_role or be the object owner to execute alter table. See the Reference Manual for the complete alter table syntax.
For example, by default, the au_lname column of the authors table uses a varchar(50) datatype. To alter the au_lname to use a varchar(60), enter:
alter table authors modify au_lname varchar(60)
You cannot use a variable as the argument to a default
that is part of an alter table statement.
Dropping, modifying, and adding non-null columns may perform a data copy, which has implications for required space and the locking scheme. See “Data copying”.
The modified table’s page chains inherits the table’s current configuration options (for example, if fillfactor is set to 50 percent, the new pages have this same fillfactor).
Adaptive
Server does partial logging (of page allocations) for alter
table operations. However, because alter table is
performed as a transaction, you cannot dump the transaction log
after running alter table; you must dump the database
to ensure it is recoverable. If the server encounters any problems during
the alter table operation, Adaptive Server rolls back the transaction.
alter table acquires an exclusive table lock while it is modifying the table schema. This lock is released as soon as the command has finished.
alter table does not fire any triggers.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
