Accuracer: The First And Only BDE Alternative
Client-Server Single-File Embedded Database for Delphi / C++Builder / Kylix



ALTER TABLE Statement

Previous  Top  Next


Introduction

The ALTER TABLE statement is used to modify a structure of the existing table.

Syntax

ALTER TABLE table_name ADD [COLUMN]
(
column_name data_type [(dimensions)] [NOT NULL]
[,column_name data_type [(dimensions)] [NOT NULL]...]
[,PRIMARY KEY (column_name [, column_name...])]
)
ALTER TABLE table_name ADD
(
[ PRIMARY KEY [key name] (column_name [ ASC | DESC ] [ CASE | NOCASE ]
[, column_name...]) ]
[ FOREIGN KEY [key name] (column_name [ { ,column_name } ... ])
REFERENCES table_name [MATCH FULL | MATCH PARTIAL]
[ON DELETE <CASCADE | SET NULL | SET DEFAULT | NO ACTION>]
[ON UPDATE <CASCADE | SET NULL | SET DEFAULT | NO ACTION>] ]
)

ALTER TABLE TableName <MODIFY> | <ALTER [COLUMN]> (
column_name data_type [(dimensions)] |
AutoInc[([data_type ]
[ , INCREMENT integer ]
[ , STARTVALUE integer ]
[ , MAXVALUE integer | NOMAXVALUE ]
[ , MINVALUE integer | NOMINVALUE ]
[ , CYCLED | NOCYCLED ]
)]

[ BLOBBLOCKSIZE {1..4294967295} ]
[ BLOBCOMPRESSIONALGORITHM {NONE | ZLIB | BZIP | PPM} ]
[ BLOBCOMPRESSIONMODE {0 .. 9} ]

[ DEFAULT {const | NULL} | DROP DEFAULT ]
[ NOT NULL | NULL ]
[ PRIMARY [KEY] | UNIQUE [ ASC | DESC ] [ CASE | NOCASE ]]
[MINVALUE value | NOMINVALUE ]
[MAXVALUE value | NOMAXVALUE ]
)


ALTER TABLE table_name DROP [COLUMN]
(
column_name [,column_name...]
)

ALTER TABLE table_name DROP CONSTRAINT constraint_name [CASCADE | RESTRICT]

ALTER TABLE TableName RENAME [COLUMN] OldName [ TO ] NewName

ALTER TABLE TableName RENAME TO NewName
or
RENAME TABLE TableName TO NewName

Use ALTER TABLE to modify a structure of the existing table.
The ADD clause is used to add new columns to the table.
The ALTER or MODIFY clauses are used to modify columns definitions.

Note: Accuracer always tries to keep existing values for the modified columns when it possible.
However, some type of conversions causes data losses - for example, if you will convert string column to the integer one, all values that cannot be converted to integer will be replaced with NULL values.

The DROP clause is used to remove columns from the table.

Here are some examples:

ALTER TABLE Test DROP (Numeric);

ALTER TABLE Test ADD (NewField WideString(500));

ALTER TABLE Test DROP CONSTRAINT PK CASCADE

ALTER TABLE Emp ADD FOREIGN KEY FKDeptID (DeptID) REFERENCES Dept MATCH FULL
ON DELETE CASCADE ON UPDATE SET DEFAULT



© AidAim Software Accuracer: