Use this statement to modify a table definition.

Syntax

ALTER TABLE table-name {
add-clause 
| modify-clause 
| drop-clause 
| rename-clause 
}

add-clause :
 ADD { column-definition | table-constraint }

modify-clause :
ALTER column-definition

drop-clause :
DROP { column-name | CONSTRAINT constraint-name }

rename-clause :   
RENAME { 
   new-table-name   
   | [ old-column-name TO ] new-column-name
   | CONSTRAINT old-constraint-name TO new-constraint-name }

column-definition :
column-name data-type
   [ [ NOT ] NULL ] 
   [ DEFAULT column-default ] 
   [ UNIQUE ]

column-default :
GLOBAL AUTOINCREMENT [ ( number ) ] 
| AUTOINCREMENT 
| CURRENT DATE 
| CURRENT TIME 
| CURRENT TIMESTAMP 
| NULL 
| NEWID( ) 
| constant-value

table-constraint :
[ CONSTRAINT constraint-name ] 
{ fkey-constraint | unique-key-constraint } 
[ WITH MAX HASH SIZE integer ]

fkey-constraint : 
[ NOT NULL ] FOREIGN KEY [ role-name ] ( ordered-column-list )
   REFERENCES table-name ( column-name, ... ) 
   [ CHECK ON COMMIT ] 

unique-key-constraint :
UNIQUE ( ordered-column-list )

 ordered-column-list :
( column-name [ ASC | DESC ], ... )

Parameters

add-clause   Adds a new column or table constraint to the table:

modify-clause   Change a single column definition. Note that you cannot use primary keys in the column-definition when part of an ALTER statement. For a full explanation of column-definition, see UltraLite CREATE TABLE statement.

drop-clause   Delete a column or a table constraint:

rename-clause   Change the name of a table, column, or constraint:

column-constraint   A column constraint restricts the values the column can hold to ensure the integrity of data in the database. A column constraint can only be UNIQUE.

UNIQUE   Identifies one or more columns that uniquely identify each row in the table. No two rows in the table can have the same values in all the named column(s). A table may have more than one unique constraint.

Remarks

Only one table-constraint or column-constraint can be added, modified, or deleted in one ALTER TABLE statement.

The role name is the name of the foreign key. The main function of the role-name is to distinguish two foreign keys to the same table. Alternatively, you can name the foreign key with CONSTRAINT constraint-name. However, do not use both methods to name a foreign key.

You cannot MODIFY a table or column constraint. To change a constraint, you must DELETE the old constraint and ADD the new constraint.

A table whose name ends with nosync can only be renamed to a table name that also ends with nosync. See Nosync tables in UltraLite.

ALTER TABLE cannot execute if a statement that affects the table is already being referenced by another request or query. Similarly, UltraLite does not process requests referencing the table while that table is being altered. Furthermore, you cannot execute ALTER TABLE when the database includes active queries or uncommitted transactions.

For UltraLite.NET users: You cannot execute this statement unless you also call the Dispose method for all data objects (for example, ULDataReader). See Dispose method.

Statements are not released if schema changes are initiated at the same time. See Schema changes with DDL statements.

See also

• UltraLite CREATE TABLE statement
• UltraLite DROP TABLE statement
• Data types in UltraLite
• Altering tables
• Using table and column constraints
• Overriding partition sizes for autoincremented columns
• Determining the most recently assigned GLOBAL AUTOINCREMENT value

Examples

The following statement drops the Street column from a fictitious table called MyEmployees.

ALTER TABLE MyEmployees
DROP Street;

The following example changes the Street column of the fictitious table, MyCustomers, to hold approximately 50 characters.

ALTER TABLE MyCustomers
MODIFY Street CHAR(50);