Difference between revisions of "ALTER TABLE"

From Lianjapedia
Jump to: navigation, search
 
(9 intermediate revisions by the same user not shown)
Line 3: Line 3:
  
 
==Syntax==
 
==Syntax==
−
ALTER [IGNORE] TABLE [<database>!]&#060;table&#062;
+
ALTER TABLE [<database>!]&#060;table&#062; <DDL-clause> [...]
  
−
ADD [COLUMN] (<column> <datatype> [<column constraints>] [,...])
+
ALTER TABLE [<database>!]&#060;table&#062; METADATA <metadata-string>
−
| &#060;table constraint&#062;
+
  
−
| ALTER | MODIFY [COLUMN] <column> [SET DEFAULT <value> | DROP DEFAULT]
+
ALTER TABLE [<database>!]&#060;table&#062; MOVE (<column>,<position>)
−
| (<column> <datatype> [<column constraint>] [,...])
+
  
−
| CONSTRAINT (<column> SET <column constraint> <value> [,...])
+
ALTER TABLE [<database>!]&#060;table&#062; RENAME (<column>,<new column>)
−
| &#060;table constraint&#062;
+
  
−
| DROP [COLUMN] <column>
+
ALTER TABLE [<database>!]&#060;table&#062; SET CHECK <condition> [ERROR <message>]
−
| (<column> [,...])
+
−
| CONSTRAINT (<column> <column constraint> [,...]) | &#060;table constraint&#062;
+
−
 
+
−
| SET CHECK <condition> [ERROR <message>]
+
−
 
+
−
| MOVE (<column>,<position>)
+
−
 
+
−
| RENAME (<column>,<new column>)
+
  
 
==See Also==
 
==See Also==
−
[[ADD TABLE]], [[ALTER INDEX]], [[ALTER VIRTUALTABLE]], [[SQL Constraints|CONSTRAINTS]], [[CREATE DATABASE]], [[CREATE INDEX]], [[CREATE TABLE]], [[CREATE TRIGGER]], [[CREATE VIRTUALTABLE]], [[SQL Data Types|DATA TYPES]], [[DELETE TRIGGER]], [[DROP DATABASE]], [[DROP INDEX]], [[DROP TABLE]], [[GETENV()]], [[SQL INSERT|INSERT]], [[OPEN DATABASE]], [[SQL SELECT|SELECT]]
+
[[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==
 
==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.
 
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:
  
−
{| class="wikitable" width="100%"
+
====ADD (<column> <datatype> [<column constraints>] [,...])====
−
!Keywords||Description
+
Add the specified column or columns.
−
|-
+
−
|valign="top"|IGNORE||If IGNORE is omitted and there are duplicate UNIQUE keys in the table, the ALTER TABLE is aborted with an error.  If IGNORE is specified, records containing a duplicate UNIQUE key are deleted, leaving only the first row with that key.
+
−
|-
+
−
|valign="top"|database||The name of the database to which the table to be altered belongs.  The '!' character must be included between the database name and the table name.
+
−
|-
+
−
|table||The name of the table to be altered.
+
−
|-
+
−
|ADD||This will insert one or more new columns into the table
+
−
|-
+
−
|COLUMN||Optional COLUMN keyword.
+
−
|-
+
−
|column||The name of the column to operate on.
+
−
|-
+
−
|valign="top"|datatype||The data type to be stored in the column, and the applicable length or precision.
+
−
|-
+
−
|column constraint||The column constraint.
+
−
|-
+
−
|table constraint||The table constraint.
+
−
|-
+
−
|valign="top"|ALTER | MODIFY||These are used to change existing column definitions and column and table constraints.
+
−
|-
+
−
|valign="top"|SET DEFAULT <expr>||Specify the DEFAULT column constraint to set a default value for the specified column.  The <expr> must evaluate to the same data type as the target column.  The column's value can subsequently be updated.
+
−
|-
+
−
|valign=Top"|DROP DEFAULT||Remove the DEFAULT column constraint for the specified column.
+
−
|-
+
−
|valign="top"|DROP||This is used to delete existing column definitions and column and table constraints.
+
−
|-
+
−
|valign="top"|CONSTRAINT||This keyword is used if the constraint refers to a column.
+
−
|-
+
−
|SET||Precedes an existing column constraint whose value is being changed.
+
−
|-
+
−
|value||The new value for the specified column constraint.
+
−
|-
+
−
|valign="top"|SET CHECK <condition> ||Specify CHECK table constraint.  This validation is activated when an operation to insert, update or delete records in the table is called. The <condition> specified must evaluate to True (.T.) for the operation to succeed.  If the <condition> evaluates to False (.F.) the operation is abandoned and the ERROR table constraint message is displayed.  If the ERROR table constraint has not been defined, a default error message is displayed.
+
−
|-
+
−
|valign="top"|ERROR <message>||Specify ERROR table constraint.  The <message> is the error message to be displayed if the CHECK table constraint evaluates to False (.F.).
+
−
|-
+
−
|MOVE||This is used to change the position of an existing column.
+
−
|-
+
−
|position||The position of the column in the structure.
+
−
|-
+
−
|RENAME||This is used to change the name of an existing column.
+
−
|-
+
−
|new column||The new name for the column.
+
−
|-
+
−
|}
+
  
 +
====ADD &#060;table constraint&#062;====
 +
Add the specified table constraint.
  
−
==Example==
+
====ADD COLUMN <column> <datatype> [<column constraints>]====
−
<code lang="recital">
+
Add the specified column.
−
// Add new column with column constraints
+
−
ALTER TABLE customer ADD COLUMN timeref CHAR(8);
+
−
  CHECK validtime(timeref);
+
−
  ERROR "Not a valid time string"
+
  
−
// Alter existing columns to add column constraints
+
====ALTER (<column> <datatype> [<column constraints>] [,...])====
−
ALTER TABLE customer;
+
Change the specified column or columns.
−
  ALTER COLUMN available CALCULATED limit-balance;
+
−
  ALTER COLUMN limit RECALCULATE;
+
−
  ALTER COLUMN balance RECALCULATE
+
  
−
//or
+
====ALTER COLUMN <column> [SET] <column constraints>====
−
ALTER TABLE customer;
+
Change the specified constraint or constraints for the specified column.
−
  ALTER (available CALCULATED limit-balance,;
+
−
  limit RECALCULATE,;
+
−
  balance RECALCULATE)
+
  
−
// Add new column, add column constraint,
+
====ALTER COLUMN <column> DROP DEFAULT====
−
// modify column datatype and drop constraints then drop column
+
Delete the DEFAULT constraint for the specified column.
−
ALTER TABLE customer ADD (timeref CHAR(8))
+
  
−
ALTER TABLE customer;
+
====ALTER CONSTRAINT (<column> <constraint> [,...])====
−
  ALTER CONSTRAINT;
+
Change the specified constraint or constraints for the specified column or columns.
−
  (timeref SET CHECK validtime(timeref);
+
 
−
  ERROR "Not a valid time string")
+
====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 &#060;table constraint&#062;====
 +
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 &#060;table constraint&#062; [,...]====
 +
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 &#060;table constraint&#062;====
 +
Change the specified table constraint, specifying the new value.
 +
 
 +
===Single Statements===
 +
The following should be issued as single ALTER TABLE statements:
 +
 
 +
====ALTER TABLE [<database>!]&#060;table&#062; METADATA <metadata-string>====
 +
Update the table's Active Data Dictionary with the specified metadata string.
 +
 
 +
====ALTER TABLE [<database>!]&#060;table&#062; MOVE (<column>,<position>)====
 +
Move the position of the specified column to the specified position.
 +
 
 +
====ALTER TABLE [<database>!]&#060;table&#062; RENAME (<column>,<new column>)====
 +
Rename the specified column to the specifed new column.
 +
 
 +
====ALTER TABLE [<database>!]&#060;table&#062; 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 customer;
 
−
  ALTER (timeref datetime);
 
−
  DROP CONSTRAINT (timeref CHECK, timeref ERROR)
 
  
−
ALTER TABLE customer DROP (timeref)
+
// Or, using ALTER TABLE ... MODIFY instead of ALTER TABLE ... ALTER
  
−
// Add an ONUPDATE table constraint
+
drop table mytable
−
ALTER TABLE customer;
+
create table mytable (field1 char(10), field2 char(10), field3 char(10))
−
  MODIFY ONUPDATE "check_update"
+
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
  
−
// Add and then remove CHECK table constraint
+
// Single Statements
−
ALTER TABLE customer SET CHECK checkit() error "Invalid operation"
+
alter table mytable;
 +
metadata "purpose=Documentation Test Table"
  
−
ALTER TABLE customer DROP CHECK
+
alter table mytable;
 +
move (uniqueid,1)
  
−
// Move position of column
+
alter table mytable;
−
CREATE TABLE temp;
+
rename (uniqueid,id)
−
(field1 char(10), field3 dec(10,2), field2 date)
+
−
ALTER TABLE temp;
+
−
MOVE (field3,3)
+
  
−
// Rename column
+
alter table mytable;
−
ALTER TABLE customer RENAME(first_name,forename)
+
set check checkproc() error "Invalid Operation"
 
</code>
 
</code>
  
Line 138: Line 164:
 
[[Category:Commands]]
 
[[Category:Commands]]
 
[[Category:Databases]]
 
[[Category:Databases]]
 +
[[Category:Metadata API]]

Latest revision as of 10:38, 27 February 2017

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"
Retrieved from "https://www.lianja.com/doc/index.php?title=ALTER_TABLE&oldid=16061"