# 8. SQL Reference

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Reference
Datatypes
User Defined Types
XML Column Type
Identifier Case & Quoting
Wide Character Identifiers
Qualified Names
Literals, Brace Escapes
CREATE TABLE Statement
DROP TABLE Statement
CREATE INDEX Statement
DROP INDEX Statement
ALTER TABLE Statement
Adding a CHECK Constraint
CREATE VIEW Statement
CREATE XML SCHEMA Statement
DROP XML SCHEMA Statement
Sequence Objects
INSERT Statement
UPDATE Statement
SELECT Statement
COMMIT WORK, ROLLBACK WORK Statement
CHECKPOINT, SHUTDOWN Statement
Stored Procedures as Views & Derived Tables
GRANT, REVOKE Statement
SET Statement
Best Effort Union
Standard and User-Defined Aggregate Functions
Virtuoso SQL Optimization
SQL Inverse Functions
SQL Grammar
Bitmap Indices

## 8.12. ALTER TABLE Statement

    add_column:
                ALTER TABLE q_table_name ADD opt_col_add_column add_col_column_def_list
              | ALTER TABLE q_table_name DROP opt_col_add_column add_col_column_list
              | ALTER TABLE q_table_name MODIFY opt_col_add_column column_def

    add_col_column_def_list: column_def
                           | add_col_column_def_list &apos;,&apos; column_def

    add_col_column_list: column
                       | add_col_column_list &apos;,&apos; column

    table_rename:
                ALTER TABLE q_table_name RENAME new_table_name

    alter_constraint:
                ALTER TABLE q_table_name constraint_op opt_table_constraint_def

    constraint_op: ADD
                 | DROP
                 | MODIFY

    opt_drop_behavior: /* empty */
                     | CASCADE
                     | RESTRICT

    opt_table_constraint_def: CONSTRAINT identifier opt_drop_behavior
                            | table_constraint_def

    view_query_spec: query_exp
                   | query_no_from_spec

    alter_table
    	: ALTER TABLE q_table_name ADD opt_col_add_column add_col_column_def_list

    	| ALTER TABLE q_table_name DROP opt_col_add_column add_col_column_list

    	| ALTER TABLE q_table_name MODIFY opt_col_add_column column_def

    	| ALTER TABLE q_table_name RENAME new_table_name

    	| ALTER TABLE <table> ADD | DROP | MODIFY <constraint> [<constraint-name>]

    <constraint> ::=
    	: PRIMARY KEY '(' <column list> ')'
    	| FOREIGN KEY '(' <column list> ')' <references>
    	| CHECK ( search-condition )

    <references> ::=
       REFERENCES <table> ['(' <column list> ')' ] <OPTIONS>

    <OPTIONS>  ::=
       [ON UPDATE OPT_ACTION] [ON DELETE OPT_ACTION]

The ALTER TABLE statement adds or drops columns and renames tables.

Adding or dropping a column of a table will create a new version of the table's schema entry. The rows of the altered table will be changed to conform to the new definition when next updated. All newly inserted rows will be in the new row layout. This means that ALTER TABLE itself executes in fixed time without locking the table. The time to update the actual data will be spread over subsequent updates.

An added column will have a NULL value on those rows where the new column has not been set. A possible default will only apply to newly inserted rows.

When dropping a column one can execute a statement of the form UPDATE <table> SET <key> = <key> to force the physical change, causing space associated with the dropped column to be freed.

The column_def in the ADD clause may specify any column options, except PRIMARY KEY.

If the table name is not fully qualified it is completed as in any table reference. The new name in the RENAME clause is defaulted to the current qualifier and user account, as in a CREATE TABLE.

A primary key may only be modified, never dropped or added. Tables always have exactly one primary key.

The first unique index of an empty table becomes the primary key. This may thereafter not be dropped.

The primary key of an attached table may not be changed.

Foreign keys can be added. Dropping a foreign key will drop all foreign keys between the foreign key and primary key tables.

ALTER TABLE cannot be applied to an attached table.

ALTER TABLE Examples

    ALTER TABLE DISTRICT
        add D_SALES_MGR integer not null references EMPLOYEES (E_ID);

    ALTER TABLE idt MODIFY PRIMARY KEY (K1, K2);

    ALTER TABLE idt ADD FOREIGN KEY (d) REFERENCES idt (d);

    ALTER TABLE idt ADD FOREIGN KEY (k2, k1) REFERENCES idt;

    ALTER TABLE idt DROP FOREIGN KEY (d) REFERENCES idt (d);

### 8.12.1. Adding a CHECK Constraint

A CHECK constraint can be added to a table after it has been created and populated providing that none of the tables contents would violate the constraint.

Adding a CHECK constraint to an existing table.

    CREATE TABLE test_add_check (
      name VARCHAR,
      age INTEGER
      )
    ;

    ALTER TABLE test_add_check ADD CONSTRAINT chkage CHECK (age > 18);

See Also:

The CHECK constraint

Previous DROP INDEX Statement | Chapter Contents | Next CREATE VIEW Statement