 |
|
|
 | Complete CREATE TABLE Statement |  | Database Tables | DROP TABLE Statement |  |
The ALTER TABLE statement modifies the structure and properties of a database table. Its syntax and function depend on the compatibility attributes settings. The syntax according to the SQL norm looks like this:
statement_ALTER_TABLE ::= ALTER TABLE [scheme.]table_name [ table_flag ] action {, action }...
table_flag ::= REC_PRIVILS
action ::= column_addon | column_removal | column_change | table_constraint_addon
| table_constraint_removal
column_addon ::= ADD [ COLUMN ] column_description
column_removal ::= DROP [ COLUMN ] column_name
column_change ::= ALTER [ COLUMN ] { column_description [ VALUES ' expression_for_value_transfer ' ]
| column_name SET DEFAULT default_value | column_name DROP DEFAULT }
table_constraint_addon ::= ADD constraint_description
table_constraint_removal ::= DROP CONSTRAINT constraint_name
constraint_description ::= [CONSTRAINT constraint_name] { index_description | internal_integrity_constraint
| referential_integrity_constraint }
Syntax compatible with the older version of 602SQL differs in the following:
action ::= column_addon | column_change | column_removal | constraint_description
column_addon ::= ADD [ order ] column_description [ VALUES ' expression_for_value_transfer ' ]
column_change ::= ALTER [ COLUMN ] column_name { column_description [ VALUES ' expression_for_value_transfer ' ]
| SET DEFAULT default_value | DROP DEFAULT }
Added, altered and deleted columns are specified in both syntax variations (see column description). The 602SQL syntax allows you to specify order - the order number of the new column in the table design (starting from 1). Moreover it allows you to specify a expression defining the value of new or altered column using the old columns, thus ensuring the transformation of data stored in the modified table.
Added or deleted constraints are further specified in the SQL syntax (see index description and integrity constraints description), while in the 602SQL syntax all constraints, that should work in the new table, are specified, other constraints are automatically deleted. You can alse specify a expression defining the value of altered column for data transformation - extension of an SQL norm. If the table has table_flags specific for 602SQL, the flags have to be specified in the SQL syntax again (they cannot be deleted). For 602SQL syntax the following holds - if they are specified, they remain unchanged, if they are not specified, they are deleted.
The compatibility attributes are set either globally from the Runtime parameters dialog on the SQL compatibility tab or dynamically using the Set_sql_option function, option SQLOPT_OLD_ALTER_TABLE (128). The compatibility attribute setting when compiling is critical for the statement functionality. Therefore it there is a statement changing compatibility attributes and the ALTER TABLE statement in a procedure one after the other, then the change of the attribute doesn't affect execution of ALTER TABLE statement.
Modifying a table using the table designer sets the compatibility attribute for 602SQL syntax.
You cannot use this statement on a temporary table.
Adding or deleting and index can be done also with the CREATE INDEX and DROP INDEX functions.
A change of table structure may affect a large variety of objects, that have a reference to the table. Stored procedures, forms, application programs etc. that have references to deleted or altered columns may cease to work.
602SQL doesn't delete the object with reference to the table neither upon the ALTER TABLE execution nor it prevents the execution of ALTER TABLE in the case, that the references exist. Instead of that, 602SQL expects that the application author puts all references into correspondence with the new table structure.
SQL statements are analyzed, optimized and compiled before their execution into a form that allows their effective execution. This phase makes use of the information about the structure of used tables. However if the table structure is changed between the SQL statement compilation and its execution, the statements will be functioning wrong.
This situation occurs, when the ALTER TABLE statement is inserted into an order of statements in a procedure or a trigger, in which the same table is modified in any way after its execution. Therefore it's not allowed to work with any data in the table that is altered using the ALTER TABLE statement until all running stored procedures and triggers are complete.
Examples of banned operations:
If it's neccessary that such statements are in a single procedure, it's necessary to postpone the compilation of statements dependent on the new table structure with the SQL_execute function.
If a table is modified with the table designer, the Development Client changes all necessary data structures according to the new table structure, thus you're able to work with the table directly. However if a procedure, that executes ALTER TABLE, is called from the client interface, the running client doesn't learn about the structure change and won't be able to work with the table until the Control Panel is refreshed or until the Development Client is restarted.
We're working with the Tab1 table. It's desired to remove the column A, add NewCol column, create a nonunique index and set referential integrity with a superior table Tab2, change the default value in the D column, create a primary key in the B column and change integrity constraint for this column.
ALTER TABLE Tab1 DROP COLUMN A, ADD COLUMN NewCol CHAR(20) COLLATE CSISTRING, ALTER COLUMN D SET DEFAULT CURRENT_DATE, ADD PRIMARY KEY (B) , ADD CONSTRAINT `Pro ref.integ.` INDEX (NewCol) , DROP CONSTRAINT `Check_B`, ADD CONSTRAINT `Check_B` CHECK (B > 0) , ADD CONSTRAINT `Tab1-Tab2` FOREIGN KEY (NewCol) REFERENCES Tab2 (Col) ON UPDATE CASCADE ON DELETE NO ACTION
List of topics:
| Complete CREATE TABLE Statement | | Database Tables | DROP TABLE Statement | |