ALTER TABLE statement [UltraLite]

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

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

modify-clause :
ALTER column-definition | sync-constraint

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 ], ... )

sync-constraint :SYNCHRONIZE {ON| OFF|ALL|DOWNLOAD}

add-clause Adds a new column or table constraint to the table:
Adds a new column or table constraint to the table:
- ADD column-definition clause Adds a new column to the table. If the column has a default value, all rows in the new column are populated with that default value. For descriptions of the keywords and subclauses for this clause, see CREATE TABLE statement [UltraLite].
- ADD table-constraint clause Adds a constraint to the table. The optional constraint name allows you to modify or drop individual constraints at a later time, rather than having to modify the entire table constraint. For descriptions of the keywords and subclauses for this clause, see CREATE TABLE statement [UltraLite].
  When adding a new unique constraint, all constraint columns must be non nullable. To add a unique constraint, alter the column to be NOT NULL.
  
  Note
  
  You cannot add a primary key in UltraLite.
modify-clause Change a single column definition. Note that you cannot use primary keys in the column-definition when part of an ALTER statement. If necessary, the data in the modified column is converted to the new data type. If a conversion error occurs, the operation will fail and the table is left unchanged. For a full explanation of column-definition, see CREATE TABLE statement [UltraLite].
drop-clause Delete a column or a table constraint:
- DROP column-name Delete the column from the table. If the column is contained in any index, uniqueness constraint, foreign key, or primary key, then the object must be deleted before UltraLite can delete the column.
- DROP CONSTRAINT table-constraint Delete the named constraint from the table definition. For a full explanation of table-constraint, see CREATE TABLE statement [UltraLite].
  
  Note
  
  You cannot drop a primary key in UltraLite.
rename-clause Change the name of a table, column, or constraint:
- RENAME new-table-name Change the name of the table to new-table-name. Note that any applications using the old table name must be modified. Foreign keys that were automatically assigned the old table name will not change names.
- RENAME old-column-name TO new-column-name Change the name of the column to the new-column-name. Note that any applications using the old column name will need to be modified.
- RENAME old-constraint-name TO new-constraint-name Change the name of the constraint to the new-constraint-name. Note that any applications using the old constraint name need to be modified.
  
  Note
  
  You cannot rename a primary key in UltraLite.
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.
sync-constraint clause Specify a sync constraint to determine whether a table can be synchronized or not and whether all rows are uploaded, just changes to the table are uploaded, or no changes to the table are uploaded.
- SYNCHRONIZE ON Default setting - the table can be synchronized and only changes to the table are sent in the upload.
- SYNCHRONIZE OFF The table cannot be synchronized and it is an error to include the table in a publication.
- SYNCHRONIZE ALL The table can be synchronized and all rows in the table are sent in the upload. This constraint is not supported by UltraLite Java edition databases.
- SYNCHRONIZE DOWNLOAD The table can be synchronized with changes to the consolidated database but no local changes are uploaded.

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.

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 ULBulkCopy.Dispose method [UltraLite.NET].

Statements are not released if schema changes are initiated at the same time.

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
ALTER Street CHAR(50);

ALTER TABLE statement [UltraLite]

Syntax

Parameters

Note

Note

Note

Remarks

See also

Examples