Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

UltraLite ALTER TABLE statement

Modifies a table definition.

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 ]
[ column-constraint ... ]

column-constraint :
[UNIQUE ]

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

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

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

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

• ADD column-definition  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.

• ADD table-constraint  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.

  Note You cannot add a primary key in UltraLite.

For a full explanation of column-definition and table-constraint, see UltraLite CREATE TABLE statement.

modify-clause    Change a single column definition.

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

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 index, constraint, or key must be deleted before the column can be deleted.

• DROP CONSTRAINT table-constraint  Delete the named constraint from the table definition.

  Note You cannot drop a primary key in UltraLite.

For a full explanation of table-constraint, see UltraLite CREATE TABLE statement.

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 in order to help 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.

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.

If the column is contained in a uniqueness constraint, a foreign key, or a primary key, then the constraint or key must be deleted before the column can be modified. 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.

• 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

The following example drops the office column from the employee table.

ALTER TABLE employee
DROP office

The following example allows the table to now hold up to 50 characters.

ALTER TABLE customer
MODIFY address CHAR(50)