Difference between revisions of "ALTER TABLE"
Yvonne.milne (Talk | contribs) |
Yvonne.milne (Talk | contribs) |
||
(6 intermediate revisions by the same user not shown) | |||
Line 3: | Line 3: | ||
==Syntax== | ==Syntax== | ||
− | ALTER | + | 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()]], [[SQL Constraints|CONSTRAINTS]], [[CREATE DATABASE]], [[CREATE INDEX]], [[CREATE TABLE]], [[CREATE TRIGGER]], [[CREATE VIRTUALTABLE]], [[SQL Data Types|DATA TYPES]], [[DATABASEMETADATA()]], [[DELETE TRIGGER]], [[DROP DATABASE]], [[DROP INDEX]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[Lianja MetaData API]], [[METADATA_DECODE()]], [[METADATA_ENCODE()]], [[METADATA_FINDTYPE()]], [[OPEN DATABASE]], [[SQL SELECT|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>==== | |
− | ALTER | + | Delete the specified constraint or constraints for the specified column. |
− | + | ||
− | + | ||
− | + | ||
− | + | ====ALTER <table constraint>==== | |
− | + | Change the specified table constraint, specifying the new value. | |
− | + | ||
− | ALTER TABLE | + | ====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== | ||
+ | <code lang="recital"> | ||
+ | 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 | ||
− | |||
− | |||
− | |||
− | ALTER TABLE | + | // 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" | |
</code> | </code> | ||
Latest revision as of 10:38, 27 February 2017
Contents
- 1 Purpose
- 2 Syntax
- 3 See Also
- 4 Description
- 4.1 DDL Clauses
- 4.1.1 ADD (<column> <datatype> [<column constraints>] [,...])
- 4.1.2 ADD <table constraint>
- 4.1.3 ADD COLUMN <column> <datatype> [<column constraints>]
- 4.1.4 ALTER (<column> <datatype> [<column constraints>] [,...])
- 4.1.5 ALTER COLUMN <column> [SET] <column constraints>
- 4.1.6 ALTER COLUMN <column> DROP DEFAULT
- 4.1.7 ALTER CONSTRAINT (<column> <constraint> [,...])
- 4.1.8 ALTER CONSTRAINT <column> [SET] <column constraints>
- 4.1.9 ALTER CONSTRAINT <column> DROP <column constraints>
- 4.1.10 ALTER <table constraint>
- 4.1.11 DROP COLUMN <column>
- 4.1.12 DROP (<column> [,...])
- 4.1.13 DROP CONSTRAINT (<column> <constraint> [,...])
- 4.1.14 DROP <table constraint> [,...]
- 4.1.15 MODIFY (<column> <datatype> [<column constraints>] [,...])
- 4.1.16 MODIFY COLUMN <column> [SET] <column constraints>
- 4.1.17 MODIFY COLUMN <column> DROP DEFAULT
- 4.1.18 MODIFY CONSTRAINT (<column> <constraint> [,...])
- 4.1.19 MODIFY CONSTRAINT <column> [SET] <column constraints>
- 4.1.20 MODIFY CONSTRAINT <column> DROP <column constraints>
- 4.1.21 MODIFY <table constraint>
- 4.2 Single Statements
- 4.1 DDL Clauses
- 5 Example
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"