ALTER TABLE

From Lianjapedia
Jump to: navigation, search

Purpose

Used to add, modify or delete table columns and constraints

Syntax

ALTER TABLE [<database>!]<table> <DDL-clause> [...]

ALTER TABLE [<database>!]<table> METADATA <metadata-string>

ALTER TABLE [<database>!]<table> MOVE (<column>,<position>)

ALTER TABLE [<database>!]<table> RENAME (<column>,<new column>)

ALTER TABLE [<database>!]<table> SET CHECK <condition> [ERROR <message>]

See Also

ADD TABLE, ALTER DATABASE, ALTER INDEX, ALTER VIRTUALTABLE, COLUMNMETADATA(), CONSTRAINTS, CREATE DATABASE, CREATE INDEX, CREATE TABLE, CREATE TRIGGER, CREATE VIRTUALTABLE, DATA TYPES, DATABASEMETADATA(), DELETE TRIGGER, DROP DATABASE, DROP INDEX, DROP TABLE, GETENV(), INSERT, Lianja MetaData API, METADATA_DECODE(), METADATA_ENCODE(), METADATA_FINDTYPE(), OPEN DATABASE, SELECT, TABLEMETADATA(), TABLEMETADATAVERSION()

Description

The ALTER TABLE command is used to add, modify or delete table columns and constraints and to rename columns. The ALTER TABLE statement automatically reloads the original data of the table back into the original columns. You must have ALTER privilege on the table. The table will be locked for EXCLUSIVE use during the operation.

DDL Clauses

The <DDL-clause> can be any of the following:

ADD (<column> <datatype> [<column constraints>] [,...])

Add the specified column or columns.

ADD <table constraint>

Add the specified table constraint.

ADD COLUMN <column> <datatype> [<column constraints>]

Add the specified column.

ALTER (<column> <datatype> [<column constraints>] [,...])

Change the specified column or columns.

ALTER COLUMN <column> [SET] <column constraints>

Change the specified constraint or constraints for the specified column.

ALTER COLUMN <column> DROP DEFAULT

Delete the DEFAULT constraint for the specified column.

ALTER CONSTRAINT (<column> <constraint> [,...])

Change the specified constraint or constraints for the specified column or columns.

ALTER CONSTRAINT <column> [SET] <column constraints>

Change the specified constraint or constraints for the specified column.

ALTER CONSTRAINT <column> DROP <column constraints>

Delete the specified constraint or constraints for the specified column.

ALTER <table constraint>

Change the specified table constraint, specifying the new value.

DROP COLUMN <column>

Delete the specified column.

DROP (<column> [,...])

Delete the specified column or columns.

DROP CONSTRAINT (<column> <constraint> [,...])

Delete the specified constraints for the specified column or columns.

DROP <table constraint> [,...]

Delete the specified table constraint or constraints.

MODIFY (<column> <datatype> [<column constraints>] [,...])

Change the specified column or columns.

MODIFY COLUMN <column> [SET] <column constraints>

Change the specified constraint or constraints for the specified column.

MODIFY COLUMN <column> DROP DEFAULT

Delete the DEFAULT constraint for the specified column.

MODIFY CONSTRAINT (<column> <constraint> [,...])

Change the specified constraint or constraints for the specified column or columns.

MODIFY CONSTRAINT <column> [SET] <column constraints>

Change the specified constraint or constraints for the specified column.

MODIFY CONSTRAINT <column> DROP <column constraints>

Delete the specified constraint or constraints for the specified column.

MODIFY <table constraint>

Change the specified table constraint, specifying the new value.

Single Statements

The following should be issued as single ALTER TABLE statements:

ALTER TABLE [<database>!]<table> METADATA <metadata-string>

Update the table's Active Data Dictionary with the specified metadata string.

ALTER TABLE [<database>!]<table> MOVE (<column>,<position>)

Move the position of the specified column to the specified position.

ALTER TABLE [<database>!]<table> RENAME (<column>,<new column>)

Rename the specified column to the specifed new column.

ALTER TABLE [<database>!]<table> SET CHECK <condition> [ERROR <message>]

Add or change the CHECK table constraint to the specified condition and optionally include the error message to be displayed when the condition evaluates to false.

Example

open database southwind
drop table mytable
create table mytable (field1 char(10), field2 char(10), field3 char(10))
use
alter table mytable;
add (limit num(5,2), balance num(5,2), uniqueid int autoinc description "Unique ID" primary key);
add onbeforeupdate "check_update";
add column available num(10,2) calculated limit - balance;
alter (limit num(10,2), balance num(10,2) recalculate);
alter column limit set default 1000 recalculate;
alter column limit drop default;
alter constraint (limit default 2000, available description "Available Credit");
alter constraint limit description "Credit Limit", balance description "Credit Balance";
alter constraint limit set not null range 2000,10000;
alter constraint limit drop range description;
alter onbeforeupdate "pre_update";
drop column field1;
drop (field2, field3);
drop constraint (balance description, available description);
drop onbeforeupdate
 
 
// Or, using ALTER TABLE ... MODIFY instead of ALTER TABLE ... ALTER
 
drop table mytable
create table mytable (field1 char(10), field2 char(10), field3 char(10))
use
alter table mytable;
add (limit num(5,2), balance num(5,2), uniqueid int autoinc description "Unique ID" primary key);
add onbeforeupdate "check_update";
add column available num(10,2) calculated limit - balance;
modify (limit num(10,2), balance num(10,2) recalculate);
modify column limit set default 1000 recalculate;
modify column limit drop default;
modify constraint (limit default 2000, available description "Available Credit");
modify constraint limit description "Credit Limit", balance description "Credit Balance";
modify constraint limit set not null range 2000,10000;
modify constraint limit drop range description;
modify onbeforeupdate "pre_update";
drop column field1;
drop (field2, field3);
drop constraint (balance description, available description);
drop onbeforeupdate
 
// Single Statements
alter table mytable;
metadata "purpose=Documentation Test Table"
 
alter table mytable;
move (uniqueid,1)
 
alter table mytable;
rename (uniqueid,id)
 
alter table mytable;
set check checkproc() error "Invalid Operation"