Changes an existing table definition. You can:
o Add columns
o Add constraints to tables or columns
o Modify columns
o Modify character sets
o Modify data types
o Delete columns
o Delete constraints
The ALTER TABLE statement can also add or delete table-specific
constraints. You can display the names for all constraints
currently associated with a table by using the SHOW TABLE
statement. Any number of constraints can be deleted and
declared at both the table and column levels. See also the ALTER
CONSTRAINT statement and the DROP CONSTRAINT. statement.
When you execute this statement, SQL modifies the named column
definitions in the table. All of the columns that you do not
mention remain unchanged. SQL defines new versions of columns
before defining constraints. Then, SQL defines and evaluates
constraints before storing them. Therefore, if columns and
constraints are defined in the same table definition, constraints
always apply to the latest version of a column.
When you change a table definition, other users see the revised
definition only when they connect to the database after you
commit the changes.
1 – Environment
You can use the ALTER TABLE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
ALTER TABLE --> <table-name> -+
+-----------------------------+
+-+-+-> ADD -+-> COLUMN col-definition ----------------+-+->
| | +-> CONSTRAINT table-constraint ----------+ |
| | +-> ( -+-> col-definition -+-> ) ---------+ |
| | +------- , <--------+ | |
| +-> ALTER COLUMN --> alter-col-definition ---------+ |
| +-> MODIFY -> ( -+-> alter-col-definition --+-> ) -+ |
| | +------------ , <----------+ | |
| +-> DROP -+-> COLUMN <column-name> ----------------+ |
| | +-> CONSTRAINT <constraint-name> --------+ |
| +-> enable-clause ---------------------------------+ |
| +-> disable-clause --------------------------------+ |
| +-> RENAME TO <new-table-name> --------------------+ |
| +-> COMMENT IS -+> '<quoted-string>' -+------------+ |
| +----- / -------------+ |
+-------------------------- <--------------------------+
col-definition =
--> <column-name> --+
+---------------+
+-> add-column-type -+---------------------------+-+-+
| +-> DEFAULT value-expr ----+ | |
| +-> column-identity ------+ | |
+---> COMPUTED BY value-expr ----------------------+ |
+----------------------------<-----------------------+
+--+-------------------++--------------------+-------+
+-> col-constraint -++-> position-clause -+ |
+----------------------------<-----------------------+
+--+------------------------+-------------------------->
+-> sql-and-dtr-clause --+
add-column-type =
--+-> data-type -----------------------------------+-->
+-> <domain-name> -------------------------------+
+-> <references-clause> -------------------------+
+-> AUTOMATIC --+-----------+--> AS value-expr --+
+-> INSERT -+
+-> UPDATE -+
column-identity =
--> IDENTITY --+--------------------------------------------------+-->
+- ( <start-with> --+----------------------+-> ) --+
+-> , <increment-by> --+
data-type =
-+-> char-data-types -----------------------------------------+-->
+-> TINYINT --------------+-----+------------+---------------+
+-> SMALLINT -------------+ +-> ( <n> ) -+ |
+-> INTEGER --------------+ |
+-> BIGINT ---------------+ |
+-> FLOAT ----------------+ |
+-> NUMBER -+----------------------------------+-------------+
| +-> ( -+-> <p> -+-+----------+-> ) + |
| +-> * ---+ +-> , <d> -+ |
+-> LIST OF BYTE VARYING --+------------+--+--------------+--+
| +-> ( <n> ) -+ +-> AS BINARY -+ |
| +-> AS TEXT ---+ |
+-> DECIMAL -++------------------------------+---------------+
+-> NUMERIC -++-> ( --> <n> +----------+-> ) + |
| +-> , <n> -+ |
+-> REAL ----------------------------------------------------+
+-> DOUBLE PRECISION ----------------------------------------+
+-> date-time-data-types ------------------------------------+
char-data-types =
-+-> CHAR -------------++------------++--------------------------------+-+->
+-> CHARACTER --------++-> ( <n> ) -++-> CHARACTER SET char-set-name -+ |
+-> CHAR VARYING -----+ |
+-> CHARACTER VARYING + |
+-> VARCHAR --+> ( <n> ) ---+--------------------------------+----------+
+-> VARCHAR2 -+ +-> CHARACTER SET char-set-name -+ |
+-> LONG VARCHAR ------------------------------------------------------+
+-> NCHAR --------------+-+------------+--------------------------------+
+-> NATIONAL CHAR ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER -+ |
+-> NCHAR VARYING --------------+-+------------+------------------------+
+-> NATIONAL CHAR VARYING ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER VARYING -+ |
+-> RAW -> ( <n> ) -----------------------------------------------------+
+-> LONG -+--------+----------------------------------------------------+
+-> RAW -+
date-time-data-types =
--+-> DATE -+----------+-----------------+-->
| +-> ANSI -+ |
| +-> VMS ---+ |
+-> TIME ---> frac --------------------+
+-> TIMESTAMP --> frac ----------------+
+-> INTERVAL ---> interval-qualifier --+
literal =
--+-> numeric-literal ----+--->
+-> string-literal -----+
+-> date-time-literal --+
+-> interval-literal ---+
col-constraint=
----+--------------------------------+-+
+> CONSTRAINT <constraint-name> -+ |
+---------------<--------------------+
+-> PRIMARY KEY -----------------+
+-> UNIQUE ----------------------+
+-> NOT NULL --------------------+
+-> NULL ------------------------+
+-> CHECK (predicate) -----------+
+-> references-clause -----------+
+--------------->----------------+
+---------------<----------------+
+----+----------------------------+-->
+--> constraint-attributes --+
references-clause =
--> REFERENCES <referenced-table-name> -+
+----------------------------------+
+-+-----------------------------------------+->
+-> ( -+> <referenced-column-name> +-> ) -+
+----------- , <------------+
constraint-attributes =
-+-> DEFERRABLE -------------+------------------------------+-+->
| +-> INITIALLY +-> IMMEDIATE --++ |
| +-> DEFERRED ---+ |
+-> NOT DEFERRABLE ---------+-------------------------+------+
| +-> INITIALLY IMMEDIATE --+ |
+-> INITIALLY IMMEDIATE ----+-------------------+------------+
| +-> DEFERRABLE -----+ |
| +-> NOT DEFERRABLE -+ |
+-> INITIALLY DEFERRED -----+-------------------+------------+
+-> DEFERRABLE -----+
position-clause =
-+-> AFTER --+--> COLUMN <column-name> ---->
+-> BEFORE -+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +-------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> -------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> -+
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS literal ----+
+-> DATATRIEVE -+
table-constraint =
---+-----------------------------------+----+
+-> CONSTRAINT <constraint-name> ---+ |
+------------------------------------------+
+--> table-constraint-clause --------------+
+------------------------------------------+
+---+---------------------------+------------->
+-> constraint-attributes --+
table-constraint-clause =
-+----------------------------------------------+-->
+-> PRIMARY KEY -> ( -+> <column-name> +-> ) --+
| +------- , <-----+ |
+-> UNIQUE -> ( -+> <column-name> +-> ) -------+
| +------- , <-----+ |
+-> CHECK (predicate) -------------------------+
+-> FOREIGN KEY -> ( -+> <column-name> +-> ) + |
+------- , <-----+ | |
+--------------------<---------------------+ |
+-> references-clause ---------->------------+
alter-col-definition =
-> <column-name> +----------------++-----------------------------++
+-> alt-col-type ++-> SET DEFAULT value-expr ---+|
+-> DEFAULT value-expr -------+|
+-> DROP DEFAULT value-expr --+|
+--------------------------- <------------------------------------+
+-+----------------------+---------+--------------------+--------+
++-> col-constraint -+-+ +-> position-clause -+ |
+-------- <---------+ |
+-------------------- <------------------------------------------+
+-+-+-----------------------------> ------------------+-+---------->
| +--> sql-and-dtr-clause --------------------------+ |
| +--> NO QUERY HEADER -----------------------------+ |
| +--> NO EDIT STRING ------------------------------+ |
| +--> NO QUERY NAME ----+-> FOR --+-> DTR --------++ |
| +--> NO DEFAULT VALUE -+ +-> DATATRIEVE -+ |
+------------------------- <--------------------------+
alt-col-type =
-+-> data-type -------------------+-+---------------------+-+-->
+-> <domain-name> --------------+ +-> column-identity --+ |
+-> COMPUTED BY value-expr --------------------------------+
+-> AUTOMATIC -+------------+----> AS value-expr ----------+
+-> INSERT --+
+-> UPDATE --+
enable-clause =
--> ENABLE ---+
+-------<-----+
+-+-> ALL TRIGGERS -----------------------------------------------+->
+-> TRIGGER <trigger-name> ------------------------------------++
+-+---------------+-+-> ALL CONSTRAINTS ----------------------++
+-> VALIDATE ---+ +-> CONSTRAINT <constraint-name> ---------+
+-> NOVALIDATE -+ +-> PRIMARY KEY --------------------------+
+-> UNIQUE ->( -+-> <column-name> -+-> ) -+
+------- , <-------+
disable-clause =
--> DISABLE ---+-> ALL TRIGGERS -------------------------+-->
+-> TRIGGER <trigger-name> ---------------+
+-> ALL CONSTRAINTS ----------------------+
+-> CONSTRAINT <constraint-name> ---------+
+-> PRIMARY KEY --------------------------+
+-> UNIQUE ->( -+-> <column-name> -+-> ) -+
+------- , <-------+
3 – Arguments
3.1 – ADD_(...)__
This alternate syntax is added for compatibility with Oracle
RDBMS.
3.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.3 – ADD_CONSTRAINT
Adds a table constraint definition. The four types of table
constraints are PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY.
3.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.
3.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.
3.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.
3.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.
3.8 – char-data-types
A valid SQL character data type. See the Data_Types HELP topic
for more information on character data types.
3.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.
3.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;
3.11 – column-name
The name of the column being added or modified.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.20 – DROP_COLUMN
Deletes the specified column. The COLUMN keyword is optional.
3.21 – DROP_CONSTRAINT
Deletes the specified column constraint or table constraint from
the table definition.
3.22 – DROP_DEFAULT
Deletes (drops) the default value of a column in a table.
3.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.
3.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).
3.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.
3.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.
3.27 – MODIFY_(...)__
This alternate syntax is added for compatibility with Oracle
RDBMS.
3.28 – NOT_NULL
Restricts values in the column to values that are not null.
3.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.
3.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.
3.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.
3.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.
3.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.
3.34 – SET_DEFAULT
Specifies a default value for the column.
3.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.
3.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.
3.37 – table-name
The name of the table whose definition you want to change.
3.38 – UNIQUE
Specifies that values in the associated column must be unique.
4 – Examples
Example 1: Adding a column to the EMPLOYEES table
SQL> ALTER TABLE EMPLOYEES ADD SALARY INTEGER(2);
Example 2: Adding a column and altering a column in the COLLEGES
table
The following example adds two columns, one with a query name to
the COLLEGES table. ALTER DOMAIN is also used to implicitly alter
the POSTAL_CODE column to accept 9 characters instead of 5.
SQL> SHOW TABLE COLLEGES;
Information for table COLLEGES
Comment on table COLLEGES:
names and addresses of colleges attended by employees
Columns for table COLLEGES:
Column Name Data Type Domain
----------- --------- ------
COLLEGE_CODE CHAR(4) COLLEGE_CODE_DOM
Primary Key constraint COLLEGES_PRIMARY_COLLEGE_CODE
COLLEGE_NAME CHAR(25) COLLEGE_NAME_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(5) POSTAL_CODE_DOM
.
.
.
SQL> ALTER TABLE COLLEGES
cont> ADD RANKING INTEGER
cont> ADD NUMBER_ALUMS INTEGER
cont> QUERY_NAME IS 'ALUMS';
SQL> ALTER DOMAIN POSTAL_CODE_DOM CHAR(9);
SQL> SHOW TABLE COLLEGES;
Information for table COLLEGES
Comment on table COLLEGES:
names and addresses of colleges attended by employees
Columns for table COLLEGES:
Column Name Data Type Domain
----------- --------- ------
COLLEGE_CODE CHAR(4) COLLEGE_CODE_DOM
Primary Key constraint COLLEGES_PRIMARY_COLLEGE_CODE
COLLEGE_NAME CHAR(25) COLLEGE_NAME_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(9) POSTAL_CODE_DOM
RANKING INTEGER
NUMBER_ALUMS INTEGER
Query Name: ALUMS
.
.
.
Example 3: Adding and modifying default values
SQL> /* Add a default value to the column HOURS_OVERTIME
***> */
SQL> create table DAILY_SALES
cont> (hours_overtime int
cont> ,hours_worked int default 0
cont> ,gross_sales int
cont> ,salesperson char(20)
cont> );
SQL>
SQL> /* Change the default value for the column HOURS_OVERTIME
***> */
SQL> alter table DAILY_SALES
cont> alter column HOURS_OVERTIME
cont> set default 0;
SQL>
SQL> /* Insert the days sales figures into the table,
***> accepting the default values for HOURS_WORKED, and
***> HOURS_OVERTIME
***> */
SQL> insert into DAILY_SALES (gross_sales, salesperson)
cont> values (2567, 'Bartlett');
1 row inserted
SQL>
SQL> table DAILY_SALES;
HOURS_OVERTIME HOURS_WORKED GROSS_SALES SALESPERSON
0 0 2567 Bartlett
1 row selected
SQL>
Example 4: Deleting a constraint from the EMPLOYEES table
To find out the name of a constraint, use the SHOW TABLES
statement. The SHOW TABLES statement shows all constraints that
refer to a table, not just those defined as part of the table's
definition. For that reason it is good practice to always use a
prefix to identify the table associated with a constraint when
you assign constraint names with the CONSTRAINT clause.
The constraint DEGREES_FOREIGN1 in this SHOW display follows that
convention to indicate that the constraint is associated with
the DEGREES, not the EMPLOYEES, table despite the constraint's
presence in the EMPLOYEES display.
SQL> SHOW TABLE EMPLOYEES
Information for table EMPLOYEES
Comment on table EMPLOYEES:
personal information about each employee
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_DOM
Primary Key constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
LAST_NAME CHAR(14) LAST_NAME_DOM
FIRST_NAME CHAR(10) FIRST_NAME_DOM
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL_DOM
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1_DOM
ADDRESS_DATA_2 CHAR(20) ADDRESS_DATA_2_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(5) POSTAL_CODE_DOM
SEX CHAR(1) SEX_DOM
BIRTHDAY DATE DATE_DOM
STATUS_CODE CHAR(1) STATUS_CODE_DOM
Table constraints for EMPLOYEES:
EMPLOYEES_PRIMARY_EMPLOYEE_ID
Primary Key constraint
Column constraint for EMPLOYEES.EMPLOYEE_ID
Evaluated on COMMIT
Source:
EMPLOYEES.EMPLOYEE_ID PRIMARY KEY
EMP_SEX_VALUES
Check constraint
Table constraint for EMPLOYEES
Evaluated on COMMIT
Source:
CHECK (
SEX IN ('M', 'F', '?')
)
EMP_STATUS_CODE_VALUES
Check constraint
Table constraint for EMPLOYEES
Evaluated on COMMIT
Source:
CHECK (
STATUS_CODE IN ('0', '1', '2', 'N')
)
Constraints referencing table EMPLOYEES:
DEGREES_FOREIGN1
Foreign Key constraint
Column constraint for DEGREES.EMPLOYEE_ID
Evaluated on COMMIT
Source:
DEGREES.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
JOB_HISTORY_FOREIGN1
Foreign Key constraint
Column constraint for JOB_HISTORY.EMPLOYEE_ID
Evaluated on COMMIT
Source:
JOB_HISTORY.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
RESUMES_FOREIGN1
Foreign Key constraint
Column constraint for RESUMES.EMPLOYEE_ID
Evaluated on COMMIT
Source:
RESUMES.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
SALARY_HISTORY_FOREIGN1
Foreign Key constraint
Column constraint for SALARY_HISTORY.EMPLOYEE_ID
Evaluated on COMMIT
Source:
SALARY_HISTORY.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)
.
.
.
SQL> ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_SEX_VALUES;
Example 5: Adding a NOT NULL constraint to the EMPLOYEES table
SQL> ALTER TABLE EMPLOYEES
cont> ALTER BIRTHDAY
cont> CONSTRAINT E_BIRTHDAY_NOT_NULL
cont> NOT NULL;
If any rows in the EMPLOYEES table have a null BIRTHDAY column,
the ALTER statement fails and none of the changes described in it
will be made.
Example 6: Altering the character set of a table column
Assume the database was created specifying the database default
character set and identifier character set as DEC_KANJI and the
national character set as KANJI. Also assume the ROMAJI column
was created in the table COLOURS specifying the identifier
character set.
SQL> SET CHARACTER LENGTH 'CHARACTERS';
SQL> SHOW TABLE (COLUMNS) COLOURS;
Information for table COLOURS
Columns for table COLOURS:
Column Name Data Type Domain
----------- --------- ------
ENGLISH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
FRENCH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
JAPANESE CHAR(4) KANJI_DOM
KANJI 4 Characters, 8 Octets
ROMAJI CHAR(8) DEC_KANJI_DOM
KATAKANA CHAR(8) KATAKANA_DOM
KATAKANA 8 Characters, 8 Octets
HINDI CHAR(8) HINDI_DOM
DEVANAGARI 8 Characters, 8 Octets
GREEK CHAR(8) GREEK_DOM
ISOLATINGREEK 8 Characters, 8 Octets
ARABIC CHAR(8) ARABIC_DOM
ISOLATINARABIC 8 Characters, 8 Octets
RUSSIAN CHAR(8) RUSSIAN_DOM
ISOLATINCYRILLIC 8 Characters, 8 Octets
SQL> ALTER TABLE COLOURS ALTER ROMAJI NCHAR(8);
SQL> SHOW TABLE (COLUMNS) COLOURS;
Information for table COLOURS
Columns for table COLOURS:
Column Name Data Type Domain
----------- --------- ------
ENGLISH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
FRENCH CHAR(8) MCS_DOM
DEC_MCS 8 Characters, 8 Octets
JAPANESE CHAR(4) KANJI_DOM
KANJI 4 Characters, 8 Octets
ROMAJI CHAR(8)
KANJI 8 Characters, 16 Octets
KATAKANA CHAR(8) KATAKANA_DOM
KATAKANA 8 Characters, 8 Octets
HINDI CHAR(8) HINDI_DOM
DEVANAGARI 8 Characters, 8 Octets
GREEK CHAR(8) GREEK_DOM
ISOLATINGREEK 8 Characters, 8 Octets
ARABIC CHAR(8) ARABIC_DOM
ISOLATINARABIC 8 Characters, 8 Octets
RUSSIAN CHAR(8) RUSSIAN_DOM
ISOLATINCYRILLIC 8 Characters, 8 Octets
SQL>
Example 7: Error displayed if table COLOURS contains data
In the following example, the column ROMAJI is defined with the
DEC_KANJI character set. If the column ROMAJI contains data
before you alter the character set of the column, SQL displays
the following error when you try to retrieve data after altering
the table.
SQL> SELECT ROMAJI FROM COLOURS;
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-CSETBADASSIGN, incompatible character sets prohibits the requested
assignment
SQL> --
SQL> -- To recover, use the ROLLBACK statement or return the column to its
SQL> -- original character set.
SQL> --
SQL> ROLLBACK;
SQL> SELECT ROMAJI FROM COLOURS;
ROMAJI
kuro
shiro
ao
aka
ki
midori
6 rows selected
SQL>
Example 8: Using the Position Clause
SQL> SHOW TABLE (COL) EMPLOYEES
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_NUMBER
Missing Value:
LAST_NAME CHAR(14) LAST_NAME
FIRST_NAME CHAR(10) FIRST_NAME
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL
Missing Value:
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1
Missing Value:
ADDRESS_DATA_2 CHAR(25) ADDRESS_DATA_2
Missing Value:
CITY CHAR(20) CITY
Missing Value:
STATE CHAR(2) STATE
Missing Value:
POSTAL_CODE CHAR(5) POSTAL_CODE
Missing Value:
SEX CHAR(1) SEX
Missing Value: ?
BIRTHDAY DATE VMS STANDARD_DATE
Missing Value: 17-NOV-1858 00:00:00.00
STATUS_CODE CHAR(1) STATUS_CODE
Missing Value: N
SQL> -- Alter the table to rearrange the order in which columns
SQL> -- are displayed.
SQL> ALTER TABLE EMPLOYEES
cont> ALTER COLUMN SEX BEFORE COLUMN LAST_NAME
cont> ALTER COLUMN BIRTHDAY BEFORE COLUMN LAST_NAME
cont> ALTER COLUMN STATUS_CODE BEFORE COLUMN LAST_NAME;
SQL> COMMIT;
SQL> -- Show the table to demonstrate that the order in which
SQL> -- columns are displayed has changed.
SQL> SHOW TABLE (COL) EMPLOYEES;
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_NUMBER
Missing Value:
SEX CHAR(1) SEX
Missing Value: ?
BIRTHDAY DATE VMS STANDARD_DATE
Missing Value: 17-NOV-1858 00:00:00.00
STATUS_CODE CHAR(1) STATUS_CODE
Missing Value: N
LAST_NAME CHAR(14) LAST_NAME
FIRST_NAME CHAR(10) FIRST_NAME
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL
Missing Value:
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1
Missing Value:
ADDRESS_DATA_2 CHAR(25) ADDRESS_DATA_2
Missing Value:
CITY CHAR(20) CITY
Missing Value:
STATE CHAR(2) STATE
Missing Value:
POSTAL_CODE CHAR(5) POSTAL_CODE
Missing Value:
Example 9: Disabling a Trigger
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
1 row deleted
SQL> -- Show that the EMPLOYEE_ID_CASCADE_DELETE trigger caused
SQL> -- records in the JOB_HISTORY table to be deleted for the
SQL> -- employee with EMPLOYEE_ID of 00164.
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
0 rows selected
SQL> -- Roll back the delete operation and alter the EMPLOYEES table
SQL> -- to disable the EMPLOYEE_ID_CASCADE_DELETE trigger.
SQL> ROLLBACK;
SQL> ALTER TABLE EMPLOYEES
cont> DISABLE TRIGGER EMPLOYEE_ID_CASCADE_DELETE;
SQL> -- Commit the alter operation and disconnect to ensure that
SQL> -- the next connection will have the trigger disabled.
SQL> COMMIT;
SQL> DISCONNECT DEFAULT;
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID ='00164';
1 row deleted
SQL> -- Show that with the trigger disabled, a deletion of
SQL> -- employee 00164 from the EMPLOYEES table does not
SQL> -- trigger a deletion for that employee from the
SQL> -- JOB_HISTORY table.
SQL> SELECT * FROM JOB_HISTORY WHERE EMPLOYEE_ID='00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
Example 10: NOT NULL constraint is dropped
The following example shows that the NOT NULL constraint is
dropped by ALTER TABLE.
SQL> create table MY_TABLE (a integer not null);
SQL>
SQL> show table (constraint) MY_TABLE
Information for table MY_TABLE
Table constraints for MY_TABLE:
MY_TABLE_A_NOT_NULL
Not Null constraint
Column constraint for MY_TABLE.A
Evaluated on UPDATE, NOT DEFERRABLE
Source:
MY_TABLE.A NOT null
Constraints referencing table MY_TABLE:
No constraints found
SQL>
SQL> alter table MY_TABLE
cont> alter column A NULL;
SQL>
SQL> show table (constraint) MY_TABLE
Information for table MY_TABLE
Table constraints for MY_TABLE:
No constraints found
Constraints referencing table MY_TABLE:
No constraints found
SQL>
Example 11: Adding an identity column to an existing table
SQL> alter table EMPLOYEES
cont> add column SEQUENCE_ID integer identity (1000, 10)
cont> comment is 'Add unique sequence number for every employee';
SQL>
SQL> show table (column) EMPLOYEES
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_NUMBER
.
.
.
SEQUENCE_ID INTEGER
Computed: IDENTITY
Comment: Add unique sequence number for every employee
SQL> select EMPLOYEE_ID, SEQUENCE_ID from employees;
EMPLOYEE_ID SEQUENCE_ID
00164 1000
00165 1010
.
.
.
00418 1970
00435 1980
00471 1990
100 rows selected
SQL>
SQL> show sequence EMPLOYEES
EMPLOYEES
Sequence Id: 2
Initial Value: 1000
Minimum Value: 1000
Maximum Value: (none)
Next Sequence Value: 2000
Increment by: 10
Cache Size: 20
No Order
No Cycle
No Randomize
Wait
Comment: column IDENTITY sequence
SQL>
Example 12: Revising a COMPUTED BY column
SQL> create table ttt (a integer, c computed by CURRENT_USER);
SQL> insert into ttt (a) values (10);
1 row inserted
SQL> select * from ttt;
A C
10 SMITH
1 row selected
SQL>
SQL> show table (column) ttt
Information for table TTT
Columns for table TTT:
Column Name Data Type Domain
----------- --------- ------
A INTEGER
C CHAR(31)
UNSPECIFIED 31 Characters, 31 Octets
Computed: by CURRENT_USER
SQL>
SQL> alter table ttt
cont> alter c
cont> computed by upper (substring (current_user from 1 for 1))
cont> || lower (substring (current_user from 2));
SQL>
SQL> show table (column) ttt
Information for table TTT
Columns for table TTT:
Column Name Data Type Domain
----------- --------- ------
A INTEGER
C VARCHAR(31)
UNSPECIFIED 31 Characters, 31 Octets
Computed: by upper (substring (current_user from 1 for 1))
|| lower (substring (current_user from 2))
SQL>
SQL> select * from ttt;
A C
10 Smith
1 row selected
SQL>