ALTER TABLE statement

User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements
ALTER TABLE statement

Function

To modify a table definition.

Syntax

     ALTER TABLE [ owner.]table-name
          ...
          ADD column-definition [column-constraint ...]
          | ADD table-constraint
          | MODIFY column-definition
          | MODIFY column-name DEFAULT default-value
          | MODIFY column-name [ NOT ] NULL
          | MODIFY column-name CHECK NULL
          | MODIFY column-name CHECK ( condition )
          | { DELETE | DROP } column-name
          | { DELETE | DROP } CHECK
          | { DELETE | DROP } UNIQUE ( column-name, ... )
          | { DELETE | DROP } PRIMARY KEY
          | { DELETE | DROP } FOREIGN KEY role-name
          | RENAME new-table-name
          | RENAME column-name TO new-column-name

Parameters

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

     column-constraint:
          UNIQUE
          | PRIMARY KEY
          | REFERENCES table-name [ ( column-name ) ] [ actions ]
          | CHECK ( condition )

     table-constraint:
          UNIQUE ( column-name, ... )
          | PRIMARY KEY ( column-name, ... )
          | CHECK ( condition )
          | foreign-key-constraint

     foreign-key-constraint:
          [ NOT NULL ] FOREIGN KEY [ role-name ] [ (column-name, ... ) ]
          ... REFERENCES table-name [ (column-name, ... ) ]
          ... [ actions ] [ CHECK ON COMMIT ]

actions:
[ ON UPDATE action ] [ ON DELETE action ]

     action:
          CASCADE
          | SET NULL
          | SET DEFAULT
          | RESTRICT

Usage

Anywhere.

Permissions

Must be the owner of the table or have DBA authority. Requires exclusive access to the table.

Side effects

Automatic commit. The MODIFY and DELETE options close all cursors for the current connection. The ISQL data window is also cleared.

Description

The ALTER TABLE statement changes table attributes (column definitions, constraints) in a table that was previously created. Note that the syntax allows a list of alter clauses; however, only one table-constraint or column-constraint can be added, modified or deleted in one ALTER TABLE statement.

ADD column-definition Add a new column to the table. The table must be empty to specify NOT NULL.

NULL values
SQL Anywhere optimizes the creation of columns which are allowed to contain the NULL value. The first column that is allowed to contain the NULL value allocates room for eight such columns, and initializes all eight to be the NULL value. (This requires no extra storage.) Thus, the next seven columns added require no changes to the rows of the table. Adding one more column will then allocate room for another eight such columns and then modify each row of the table to allocate the extra space. Consequently, seven out of eight column additions run quickly.

ADD table-constraint Add a constraint to the table. See "CREATE TABLE statement" for a full explanation of table constraints.

If PRIMARY KEY is specified, the table must not already have a primary key created by the CREATE TABLE statement or another ALTER TABLE statement.

MODIFY column-definition Change the length or data type of an existing column in a table. If NOT NULL is specified, a NOT NULL constraint is added to the named column. Otherwise, the NOT NULL constraint for the column will not be changed. If necessary, the data in the modified column will be converted to the new data type. If a conversion error occurs, the operation will fail and the table will be left unchanged.

Deleting an index, constraint, or 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. If a primary key is deleted, all foreign keys referencing the table will also be deleted.You cannot MODIFY a table or column constraint. To change a constraint, you must DELETE the old constraint and ADD the new constraint.

MODIFY column-name DEFAULT default-value Change the default value of an existing column in a table. To remove a default value for a column, specify DEFAULT NULL.

MODIFY column-name [ NOT ] NULL Change the NOT NULL constraint on the column to allow or disallow NULL values in the column.

MODIFY column-name CHECK NULL Delete the check constraint for the column. This statement cannot be used on databases created before Release 5.0.

MODIFY column-name CHECK ( condition ) Replace the existing CHECK condition for the column with the one specified. This statement cannot be used on databases created before SQL Anywhere 5.0.

DELETE 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. This does not delete CHECK constraints that refer to the column.

DELETE CHECK Delete all check constraints for the table. This includes both table check constraints and column check constraints.

DELETE UNIQUE (column-name,...) Delete a uniqueness constraint for this table. Any foreign keys referencing this uniqueness constraint (rather than the primary key) will also be deleted.

DELETE PRIMARY KEY Delete the primary key constraint for this table. All foreign keys referencing the primary key for this table will also be deleted.

DELETE FOREIGN KEY role-name Delete the foreign key constraint for this table with the given role name.

RENAME new-table-name Change the name of the table to the new-table-name. Note that any applications using the old table name will need to be modified. Also, any foreign keys which were automatically assigned the same name as the old table name will not change names.

RENAME 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.

ALTER TABLE will be prevented whenever the statement affects a table that is currently being used by another connection. ALTER TABLE can be time consuming and the server will not process requests referencing the same table while the statement is being processed.

Before Version 5.0, all table and column constraints were held in a single table constraint. Consequently, for these databases individual constraints on columns cannot be deleted using the MODIFY column-name CHECK NULL clause or replaced using the MODIFY column-name CHECK (condition ) clause. To use these statements, the entire table constraint should be deleted and the constraints added back using the MODIFY column-name CHECK ( condition ) clause.

Examples

Add a new column to the employees table showing which office they work in.

     ALTER TABLE employee
     ADD office CHAR(20) DEFAULT 'Boston'

Drop the office column from the employees table.

     ALTER TABLE employee
     DELETE office

The address column in the customer table can currently hold up to 35 characters. Allow it to hold up to 50 characters.

     ALTER TABLE customer
     MODIFY address CHAR(50)

Add a column to the customer table assigning each customer a sales contact.

     ALTER TABLE customer
     ADD sales_contact INTEGER
     REFERENCES employee (emp_id)
     ON UPDATE CASCADE
     ON DELETE SET NULL

This foreign key is constructed with a cascading updates and is set null on deletes. If an employee has their employee ID changed, the column is updated to reflect this change. If an employee leaves the company and has their employee ID deleted, the column is set to NULL.