Difference between revisions of "ALTER TABLE"
From Lianjapedia
Yvonne.milne (Talk | contribs) |
Helen.george (Talk | contribs) |
||
| Line 31: | Line 31: | ||
| − | {| class="wikitable" | + | {| class="wikitable" width=100% |
!Keywords||Description | !Keywords||Description | ||
|- | |- | ||
| − | |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"|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. |
|- | |- | ||
| − | |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. | + | |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. | |table||The name of the table to be altered. | ||
| Line 46: | Line 46: | ||
|column||The name of the column to operate on. | |column||The name of the column to operate on. | ||
|- | |- | ||
| − | |datatype||The data type to be stored in the column, and the applicable length or precision. | + | |valign="top"|datatype||The data type to be stored in the column, and the applicable length or precision. |
|- | |- | ||
|column constraint||The column constraint. | |column constraint||The column constraint. | ||
| Line 52: | Line 52: | ||
|table constraint||The table constraint. | |table constraint||The table constraint. | ||
|- | |- | ||
| − | |ALTER | MODIFY||These are used to change existing column definitions and column and table constraints. | + | |valign="top"|ALTER | MODIFY||These are used to change existing column definitions and column and table constraints. |
|- | |- | ||
| − | |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"|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. |
|- | |- | ||
| − | |DROP DEFAULT||Remove the DEFAULT column constraint for the specified column. | + | |valign=Top"|DROP DEFAULT||Remove the DEFAULT column constraint for the specified column. |
|- | |- | ||
| − | |DROP||This is used to delete existing column definitions and column and table constraints. | + | |valign="top"|DROP||This is used to delete existing column definitions and column and table constraints. |
|- | |- | ||
| − | |CONSTRAINT||This keyword is used if the constraint refers to a column. | + | |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. | |SET||Precedes an existing column constraint whose value is being changed. | ||
| Line 66: | Line 66: | ||
|value||The new value for the specified column constraint. | |value||The new value for the specified column constraint. | ||
|- | |- | ||
| − | |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"|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. |
|- | |- | ||
| − | |ERROR <message>||Specify ERROR table constraint. The <message> is the error message to be displayed if the CHECK table constraint evaluates to False (.F.). | + | |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. | |MOVE||This is used to change the position of an existing column. | ||
Revision as of 11:36, 21 January 2013
Contents
Purpose
Used to add, modify or delete table columns and constraints
Syntax
ALTER [IGNORE] TABLE [<database>!]<table>
ADD [COLUMN] (<column> <datatype> [<column constraints>] [,...]) | <table constraint>
| ALTER | MODIFY [COLUMN] <column> [SET DEFAULT <value> | DROP DEFAULT] | (<column> <datatype> [<column constraint>] [,...])
| CONSTRAINT (<column> SET <column constraint> <value> [,...]) | <table constraint>
| DROP [COLUMN] <column> | (<column> [,...]) | CONSTRAINT (<column> <column constraint> [,...]) | <table constraint>
| SET CHECK <condition> [ERROR <message>]
| MOVE (<column>,<position>)
| RENAME (<column>,<new column>)
See Also
ADD TABLE, ALTER INDEX, ALTER VIRTUALTABLE, CONSTRAINTS, CREATE DATABASE, CREATE INDEX, CREATE TABLE, CREATE TRIGGER, CREATE VIRTUALTABLE, DATA TYPES, DELETE TRIGGER, DROP DATABASE, DROP INDEX, DROP TABLE, GETENV(), INSERT, OPEN DATABASE, SELECT
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.
| Keywords | Description |
|---|---|
| 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. |
| 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. |
| 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. |
| ALTER | MODIFY | These are used to change existing column definitions and column and table constraints. |
| 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. |
| DROP DEFAULT | Remove the DEFAULT column constraint for the specified column. |
| DROP | This is used to delete existing column definitions and column and table constraints. |
| 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. |
| 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. |
| 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. |
Example
// 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 TABLE customer; ALTER COLUMN available CALCULATED limit-balance; ALTER COLUMN limit RECALCULATE; ALTER COLUMN balance RECALCULATE //or ALTER TABLE customer; ALTER (available CALCULATED limit-balance,; limit RECALCULATE,; balance RECALCULATE) // Add new column, add column constraint, // modify column datatype and drop constraints then drop column ALTER TABLE customer ADD (timeref CHAR(8)) ALTER TABLE customer; ALTER CONSTRAINT; (timeref SET CHECK validtime(timeref); ERROR "Not a valid time string") ALTER TABLE customer; ALTER (timeref datetime); DROP CONSTRAINT (timeref CHECK, timeref ERROR) ALTER TABLE customer DROP (timeref) // Add an ONUPDATE table constraint ALTER TABLE customer; MODIFY ONUPDATE "do check_update" // Add and then remove CHECK table constraint ALTER TABLE customer SET CHECK checkit() error "Invalid operation" ALTER TABLE customer DROP CHECK // Move position of column CREATE TABLE temp; (field1 char(10), field3 dec(10,2), field2 date) ALTER TABLE temp; MOVE (field3,3) // Rename column ALTER TABLE customer RENAME(first_name,forename)
