1 – ADD_(...)__
This alternate syntax is added for compatibility with Oracle
RDBMS.
2 – ADD_COLUMN
Creates an additional column in the table. SQL adds the column
after all existing columns in the table unless the position-
clause relocates the new column. The column definition specifies
a data type or domain name, optional default value, optional
column constraints, and optional formatting clauses.
The COLUMN keyword is optional.
3 – ADD_CONSTRAINT
Adds a table constraint definition. The four types of table
constraints are PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY.
4 – AFTER_COLUMN
Changes the normal field ordering of columns to make the
displayed column ordering more readable. Note that this does
not change the on-disk layout of the columns. By default, when
neither of these clauses is specified, columns are positioned at
the end of the table specified with the ALTER TABLE statement.
5 – ALTER COLUMN alter col definition
Modifies the column specified by the column name. The COLUMN
keyword is optional.
You can modify some elements of a column definition but not
others.
You cannot change an existing column constraint. However, you can
delete the existing constraint and add a new column constraint
using the alter-col-definition clause to achieve the same result.
6 – AUTOMATIC
Syntax options:
AUTOMATIC AS value-expr | AUTOMATIC INSERT AS value-expr |
AUTOMATIC UPDATE AS value-expr
These AUTOMATIC column clauses allow you to store special
information when data is inserted into a row or a row is updated.
For example, you can log application-specific information to
audit activity or provide essential values, such as time stamps
or unique identifiers for the data.
The assignment of values to these types of columns is managed by
Oracle Rdb. The AUTOMATIC INSERT clause can be used to provide
a complex default for the column when the row is inserted; it
cannot be changed by an UPDATE statement. The AUTOMATIC UPDATE
clause can be used to provide an updated value during an UPDATE
statement. The AUTOMATIC clause is the default and specifies that
the value expression should be applied during both INSERT and
UPDATE statements. The column type is derived from the AS value-
expr; using CAST allows a specific data type to be specified.
However, this is not required and is rarely necessary.
You can define an AUTOMATIC INSERT column to automatically
receive data during an insert operation. The data is stored like
any other column, but the column is read-only. Because AUTOMATIC
columns are treated as read-only columns, they cannot appear in
the column list for an insert operation nor be modified by an
update operation. AUTOMATIC UPDATE columns can have an associated
default value that will be used when the row is inserted.
Suppose that you want to store the current time stamp of a
transaction and supply a unique numeric value for an order
number. In addition, when the row is updated (the order is
altered), you want a new time stamp to be written to the LAST_
UPDATED column. You could write an application to supply this
information, but you could not guarantee the desired behavior.
For instance, a user with access to the table might update
the table with interactive SQL and forget to enter a new time
stamp to the LAST_UPDATED column. If you use an AUTOMATIC column
instead, it can be defined so that columns automatically receive
data during an insert operation. The data is stored like any
other column, but the column is read-only.
7 – BEFORE_COLUMN
Changes the normal field ordering of columns to make the
displayed column ordering more readable. Note that this does
not change the on-disk layout of the columns. By default, when
neither of these clauses is specified, columns are positioned at
the end of the table specified with the ALTER TABLE statement.
8 – char-data-types
A valid SQL character data type. See the Data_Types HELP topic
for more information on character data types.
9 – CHECK (predicate)
Specifies a predicate that column values inserted into the
table must satisfy. See Predicates for details on specifying
predicates.
Predicates in CHECK column constraints can only refer directly to
the column with which they are associated.
10 – col-constraint
Specifies a constraint that column values inserted into the table
must satisfy. You can specify more than one column constraint.
For example:
SQL> ALTER TABLE EMPLOYEE
cont> ADD ID_NUMBER INT NOT NULL UNIQUE;
You can name each constraint. For example:
SQL> ALTER TABLE EMPLOYEE
cont> ADD ID_NUMBER INT
cont> CONSTRAINT A NOT NULL
cont> CONSTRAINT B UNIQUE;
11 – column-name
The name of the column being added or modified.
12 – COMPUTED_BY
Specifies that the value of this column is calculated from values
in other columns and constant expressions. See the CREATE TABLE
statement for more information.
13 – constraint-attributes
Although the constraint attribute syntax provides 11 permutations
as required by the SQL99 standard, they equate to the following
three options:
o INITIALLY IMMEDIATE NOT DEFERRABLE
Specifies that evaluation of the constraint must take place
when the INSERT, DELETE, or UPDATE statement executes. If
you are using the SQL92, SQL99, MIA, ORACLE LEVEL1 or ORACLE
LEVEL2 dialect, this is the default.
This clause is the same as the NOT DEFERRABLE option provided
in previous releases of Oracle Rdb.
o INITIALLY DEFERRED DEFERRABLE
Specifies that evaluation of the constraint can take place
at any later time. Unless otherwise specified, evaluation of
the constraint takes place as the COMMIT statement executes.
You can use the SET ALL CONSTRAINTS statement to have all
constraints evaluated earlier. See the SET_ALL_CONSTRAINTS
Help topic for more information. If you are using the default
SQLV40 dialect, this is the default constraint attribute.
When using this default dialect, Oracle Rdb displays a
deprecated feature message for all constraints defined without
specification of one of the constraint attributes.
This clause is the same as the DEFERRABLE option provided in
previous releases of Oracle Rdb.
o INITIALLY IMMEDIATE DEFERRABLE
Specifies that evaluation of the constraint be deferred (using
the SET CONSTRAINT ALL statement or the SET TRANSACTION
statement with the EVALUATING clause) but by default it
is evaluated after the INSERT, DELETE, or UPDATE statement
executes.
14 – CONSTRAINT constraint name
The CONSTRAINT clause specifies a name for the table constraint.
The name is used for a variety of purposes:
o The INTEG_FAIL error message specifies the name when an
INSERT, UPDATE, or DELETE statement violates the constraint.
o The ALTER CONSTRAINT, DROP CONSTRAINT and ALTER TABLE DROP
CONSTRAINT statements specify the constraint name.
o The SHOW TABLE statements display the names of constraints.
o The EVALUATING clause of the SET and the DECLARE TRANSACTION
statements specifies constraint names.
The CONSTRAINT clause is optional. If you omit the constraint
name, SQL creates a name. However, Oracle Rdb recommends that
you always name column and table constraints. The constraint
names generated by SQL may be obscure. If you supply a constraint
name with the CONSTRAINT clause, the name must be unique in the
schema.
15 – data-type
A valid SQL data type. Specifying an explicit data type to
associate with a column is an alternative to specifying a domain
name.
See the Data_Types HELP topic for more information on data types.
Using the ALTER clause to change the data type of a column
(directly or indirectly by specifying a domain) requires caution:
o If you change a column to a character data type with a larger
capacity, or increase the scale factor for a column, or change
the character set, you may have to modify source programs that
refer to the column and precompile them again.
o If you change a column to a smaller capacity numeric data
type then overflow errors may result at run time as Oracle Rdb
attemps to convert the large value to the new data type.
o If you change a column to a data type with a smaller capacity,
SQL truncates values already stored in the database that
exceed the capacity of the new data type, but only when it
retrieves those values. (The values are not truncated in
the database, however, until they are updated. If you only
retrieve data, you can change the data type back to the
original, and SQL again retrieves the entire original value.)
o You can change a DATE column only to a character data type
(CHAR, VARCHAR, LONG VARCHAR, NCHAR, NATIONAL CHAR, NCHAR
VARYING, or NATIONAL CHAR VARYING, or date/time (DATE ANSI,
TIMESTAMP, TIME). If you attempt to change a DATE column to
anything else, SQL returns an error message.
16 – date-time-data-types
A valid SQL date-time data type. See the Data_Types HELP topic
for more information on date-time data types.
17 – disable-clause
Allows you to enable or disable all triggers, specified triggers,
all constraints, specified constraints, a primary key, or a
unique constraint, as described in the following list. By
default, table and column constraints added during an alter table
operation are enabled.
o DISABLE ALL TRIGGERS
All triggers defined for the table are disabled. (No error is
raised if no triggers are defined for this table.)
o ENABLE ALL TRIGGERS
All triggers defined for the table are enabled. (No error is
raised if no triggers are defined for this table.)
o DISABLE TRIGGER trigger-name
The named trigger for this table is disabled. The named
trigger must be defined on the table.
o ENABLE TRIGGER trigger-name
The named trigger for this table is enabled. The named trigger
must be defined on the table.
o DISABLE ALL CONSTRAINTS
All table and column constraints for this table are disabled.
(No error is raised if no constraints are defined on the
table.)
o ENABLE ALL CONSTRAINTS
All table and column constraints for this table are enabled.
(No error is raised if no constraints are defined on the
table.)
o DISABLE CONSTRAINT constraint-name
The named constraint is disabled. The named constraint must be
a table or column constraint for the table.
o ENABLE CONSTRAINT constraint-name
The named constraint is enabled. The named constraint must be
a table or column constraint for the table.
o DISABLE PRIMARY KEY
The primary key for the table is disabled.
o ENABLE PRIMARY KEY
The primary key for the table is enabled.
o DISABLE UNIQUE (column-name)
The matching UNIQUE constraint is disabled. The columns listed
must be columns in the table.
o ENABLE UNIQUE (column-name)
The matching UNIQUE constraint is enabled. The columns listed
must be columns in the table.
o VALIDATE and NOVALIDATE
These options are available only on the enable-clause. By
default, table and column constraints are enabled during an
ALTER TABLE statement. When a constraint is added or enabled
with the ALTER TABLE statement, the default is to validate
the table contents. The ENABLE NOVALIDATE option allows a
knowledgeable database administrator to avoid the time and I/O
resources required to revalidate the data when he or she knows
the data is valid.
NOTE
Oracle Corporation recommends that you use the RMU Verify
command with the Constraint qualifier periodically to
verify that your assumptions are correct if you use the
ENABLE NOVALIDATE option.
18 – DEFAULT value-expr
Provides a default value for a column if the row that is inserted
does not include a value for that column.
You can use any value expression including subqueries,
conditional, character, date/time, and numeric expressions as
default values. See Value Expressions for more information about
value expressions.
For more information about NULL, see the NULL_Keyword HELP topic.
You can add a default value to an existing column or alter
the existing default value of a column by altering the table.
However, doing so has no effect on the values stored in existing
rows.
The value expressions described in Value Expressions include
DBKEY and aggregate functions. However, the DEFAULT clause is
not a valid location for referencing a DBKEY or an aggregate
function. If you attempt to reference either, you receive a
compile-time error.
If you do not specify a default value, a column inherits the
default value from the domain. If you do not specify a default
value for either the column or domain, SQL assigns NULL as the
default value.
If you specify a default value for either the column or domain
when a column is added, SQL propagates the default value from the
column or domain to all previously stored rows. Therefore, when
you add a column to a table and specify a default value for the
column, SQL stores the default value in the newly added column
of all the previously stored rows. Likewise, if the newly added
column is based upon a domain that specifies a default value, SQL
stores the default value in the column of all previously stored
rows.
Because SQL updates data when you add a column with a default
value other than NULL, the ALTER TABLE statement can take some
time to complete when the table contains many rows. (If you
specify a default value of NULL, SQL does not modify the data
because SQL automatically returns a null value for columns that
have no actual value stored in them.) If you want to add more
than one column with default values, add them in a single ALTER
TABLE statement. When you do so, SQL scans the table data once
instead of many times.
Because data is added to the rows, adding a column with a default
value may result in fragmented records. For information about
locating and correcting record fragmentation, see the Oracle Rdb7
Guide to Database Performance and Tuning.
19 – domain-name
The name of a domain created in a CREATE DOMAIN statement. SQL
gives the column the data type specified in the domain. For more
information on domains, see the CREATE DOMAIN. statement.
For most purposes, specify a domain instead of an explicit data
type.
o Domains ensure that columns in multiple tables that serve the
same purpose all have the same data type. For example, several
tables in the sample personnel database refer to the domain
ID_DOM.
o A domain lets you change the data type for all the columns
that refer to it in one operation by changing the domain
itself with an ALTER DOMAIN statement. For example, if you
want to change the data type for the column EMPLOYEE_ID from
CHAR(5) to CHAR(6), you need only alter the data type for ID_
DOM. You do not have to alter the data type for the column
EMPLOYEE_ID in the tables DEGREES, EMPLOYEES, JOB_HISTORY, and
SALARY_HISTORY, nor do you have to alter the column MANAGER_ID
in the DEPARTMENTS table.
However, you might not want to use domains when you create tables
if:
o Your application must be compatible with the Oracle RDBMS
language.
o You are creating tables that do not need the advantages of
domains.
20 – DROP_COLUMN
Deletes the specified column. The COLUMN keyword is optional.
21 – DROP_CONSTRAINT
Deletes the specified column constraint or table constraint from
the table definition.
22 – DROP_DEFAULT
Deletes (drops) the default value of a column in a table.
23 – enable-clause
Allows you to enable or disable all triggers, specified triggers,
all constraints, specified constraints, a primary key, or a
unique constraint, as described in the following list. By
default, table and column constraints added during an alter table
operation are enabled.
o DISABLE ALL TRIGGERS
All triggers defined for the table are disabled. (No error is
raised if no triggers are defined for this table.)
o ENABLE ALL TRIGGERS
All triggers defined for the table are enabled. (No error is
raised if no triggers are defined for this table.)
o DISABLE TRIGGER trigger-name
The named trigger for this table is disabled. The named
trigger must be defined on the table.
o ENABLE TRIGGER trigger-name
The named trigger for this table is enabled. The named trigger
must be defined on the table.
o DISABLE ALL CONSTRAINTS
All table and column constraints for this table are disabled.
(No error is raised if no constraints are defined on the
table.)
o ENABLE ALL CONSTRAINTS
All table and column constraints for this table are enabled.
(No error is raised if no constraints are defined on the
table.)
o DISABLE CONSTRAINT constraint-name
The named constraint is disabled. The named constraint must be
a table or column constraint for the table.
o ENABLE CONSTRAINT constraint-name
The named constraint is enabled. The named constraint must be
a table or column constraint for the table.
o DISABLE PRIMARY KEY
The primary key for the table is disabled.
o ENABLE PRIMARY KEY
The primary key for the table is enabled.
o DISABLE UNIQUE (column-name)
The matching UNIQUE constraint is disabled. The columns listed
must be columns in a unique constraint for the table.
o ENABLE UNIQUE (column-name)
The matching UNIQUE constraint is enabled. The columns listed
must be columns in a unique constraint for the table.
o VALIDATE and NOVALIDATE
When a constraint is added or enabled with the ALTER TABLE
statement, the default is to validate the table contents.
The ENABLE NOVALIDATE option allows a knowledgeable database
administrator to avoid the time and I/O resources required to
revalidate the data when they know the data is valid.
NOTE
Oracle Corporation recommends that you use the RMU Verify
command with the Constraint qualifier periodically to
verify that your assumptions are correct if you use the
ENABLE NOVALIDATE option.
24 – FOREIGN_KEY
The name of a column or columns that you want to declare as
a foreign key in the table you are altering (the referencing
table).
25 – IDENTITY
Specifies that the column is to be a special read-only identity
column. INSERT will evaluate this column and store a unique value
for each row inserted. Only one column of a table may have the
IDENTITY attribute. Oracle Rdb creates a sequence with the same
name as the current table.
See the ALTER SEQUENCE statement and the CREATE SEQUENCE
statement for more information.
26 – increment-by
An integer literal value that specifies the increment for the
sequence created for the IDENTITY column. A negative value
creates a descending sequence, and a positive value creates an
ascending sequence. A value of zero is not permitted. If omitted
the default is 1, that is, an ascending sequence.
27 – MODIFY_(...)__
This alternate syntax is added for compatibility with Oracle
RDBMS.
28 – NOT_NULL
Restricts values in the column to values that are not null.
29 – NULL
Specifies that NULL is permitted for the column. This is the
default behavior. A column with a NULL constraint cannot also
have a NOT NULL constraint within the same ALTER TABLE statement.
However, no checks are performed for CHECK constraints, which may
limit the column to non-null values.
The NULL constraint is not stored in the database and is provided
only as a syntactic alternative to NOT NULL.
When used on ALTER TABLE . . . ALTER COLUMN this clause drops any
NOT NULL constraints defined for the column.
30 – PRIMARY_KEY
A primary key constraint defines one or more columns whose values
make a row in a table different from all others. SQL requires
that values in a primary key column be unique and not null;
therefore, you need not specify the UNIQUE and NOT NULL column
constraints for primary key columns.
You cannot specify the primary key constraint for a computed
column.
When used as a table constraint this clause must be followed by
a list of column names. When used as a column constraint this
clause applies to the named column of the table.
31 – referenced-column-name
For a column constraint, the name of the column that is a
unique key or a primary key in the referenced table. For a table
constraint, the referenced column name is the name of the column
or columns that are a unique key or primary key in the referenced
table. If you omit the referenced-column-name clause, the primary
key is selected by default.
32 – references-clause
Specifies the name of the column or columns that are a unique
key or primary key or in the referenced table. When the
REFERENCES clause is used as a table constraint, the column names
specified in the FOREIGN KEY clause become a foreign key for the
referencing table.
When used as the column type clause, specifies that the type
of the column be inherited from the PRIMARY KEY or UNIQUE index
referenced. Both the data type and domain are inherited.
33 – RENAME_TO
Changes the name of the table being altered. See the RENAME
statement for further discussion. If the new name is the name
of a synonym then an error will be raised.
The new name must not exist as the name of an existing table,
synonym, sequence or view. You may not rename a system table.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE SYNONYMS ARE ENABLED
clause. Note that these synonyms may be deleted if they are no
longer used by database definitions or applications.
34 – SET_DEFAULT
Specifies a default value for the column.
35 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clause. See the DATATRIEVE
HELP topic for more information.
If you specify a formatting clause for a column that is based on
a domain that also specifies a formatting clause, the formatting
clause in the table definition overrides the one in the domain
definition.
36 – start-with
An integer literal value that specifies the starting value for
the sequence created for the IDENTITY column. If omitted the
default is 1.
37 – table-name
The name of the table whose definition you want to change.
38 – UNIQUE
Specifies that values in the associated column must be unique.