ALTER TABLE MODIFY
COLUMN
Modifies
the definition of an existing column in a table.
SYNTAX
| table_name |
Name
of the table you are modifying the column on. |
| column_name |
Name
of the column you are modifying. |
| column_definition |
New
definition for the column you want to alter. |
DESCRIPTION
The ALTER
TABLE MODIFY COLUMN command modifies the definition of existing columns
in a table. To execute the ALTER TABLE MODIFY COLUMN command on a table,
you must be the table owner, have DBA or SYSADM security privileges, or
have the ALTER privilege for that table.
To specify
a column definition, you must provide at least a column name and a data
type or domain. You may modify multiple columns in a single command, up
to the maximum number of columns permitted in a table (252). The syntax
and keyword usage used in column definitions is shown on the following
pages.
Column Definitions
| column_name |
Name
of the modified column. |
| data_type |
Data
type to use for the modified column. |
| domain_name |
Name
of the domain to use for the modified column. |
| constant |
Constant
value to use if no value is inserted. |
| function_name |
Built-in
function to use if no value is inserted. |
| boolean_exp |
Expression
that evaluates to true or false. |
| column_name_a |
Name
of the column the modified column will be positioned after. |
| column_name_b |
Name
of the column the modified column will be positioned before. |
You must
specify a data type for each column you modify. DBMaker supports the following
data types: CHAR, INTEGER, SMALLINT, FLOAT, DOUBLE, DECIMAL, DATE, TIME,
TIMESTAMP, VARCHAR, BINARY, LONG VARCHAR, LONG VARBINARY, SERIAL, OID,
and FILE.
Optionally,
you can specify a user-defined domain for the column instead of a data
type. Domains are a combination of data type, default value, and constraint
that are applied to a column when it is defined using a domain as the
data type. (See the DEFAULT and CHECK keywords below for a description
of default values and constraints.) Default values and constraints provided
in the column definition will override those of the domain. Column definitions
can also provide constraints in addition to those of the domain.
The NULL/NOT
NULL keywords are optional. These keywords specify whether a column can
contain a NULL value (i.e. can be left empty) when inserting a new row.
The NULL keyword specifies that a column may contain an undefined value
when a new row is inserted, while the NOT NULL keyword specifies that
a value must be provided when a new row is inserted. The NOT NULL keyword
cannot be used when modifying a column that was previously defined with
NULL unless the table is empty, or you use the GIVE keyword to provide
a value for those rows containing NULL values, since any existing rows
that contain NULL values will violate the NOT NULL rule.
The DEFAULT
keyword is optional. This keyword is used to specify a default value that
will be inserted into a column if no value is provided when inserting
a new row. Constants, results from built-in functions, or the NULL keyword
may be used as the default value. You can only use built-in functions
that have no argument, such as PI(), NOW(), or USER(), when defining a
column. If you use the NULL keyword as the DEFAULT value, the column cannot
be defined with the NOT NULL keyword. The DEFAULT keyword is not normally
required when using user-defined domains instead of the standard DBMaker
data types, since domains normally include their own DEFAULT clause.
The CHECK
keyword is optional. This keyword is used to specify a range of acceptable
values (constraints) that may be entered in a column. The expression that
specifies the range of acceptable values may be any expression that evaluates
to true or false. The VALUE keyword may be used in the expression in conjunction
with the CHECK keyword to represent the value of the column. If an SQL
statement does not satisfy the CHECK conditions, it will not be processed.
The CHECK keyword is not normally required when using user-defined domains
instead of the standard DBMaker data types, since domains normally include
their own CHECK clause.
The GIVE
keyword is optional. This keyword is used to specify the value inserted
into the modified column for any existing rows that contain NULL values.
If you modify a column from NULL to NOT NULL and do not provide a value
using the GIVE keyword, DBMaker will not modify the column. Constants,
results from built-in functions, or the NULL keyword may be used as the
GIVE value. If you use the NULL keyword as the GIVE value, the column
cannot be defined with the NOT NULL keyword. You can also use the SEQUENTIAL/SEQ
keywords with the GIVE keyword when you modify a column to a SERIAL column.
These keywords specify that DBMaker will insert serial values into existing
rows, starting with the value specified by the definition of the SERIAL
data type in the column definition. The serial values will continue to
increment as you insert new rows.
The BEFORE/AFTER
keywords are optional. These keywords specify the location you want to
position the modified column relative to another column. The BEFORE keyword
specifies DBMaker should position the modified column before (to the immediate
left of) the specified column. The AFTER keyword specifies DBMaker should
position the modified column after (to the immediate right of) the specified
column. If you do not specify a relative location using the BEFORE/AFTER
keywords, DBMaker will simply leave the column in the original position.
Modifying
a column in a table makes all views and stored commands defined on the
table invalid, but has no effect on any synonyms based on that table.
Column names have a maximum length of eighteen characters, and may contain
letters, numbers, the underscore character, and the symbols $ and #. The
first character may not be a number.
EXAMPLES
The following
example modifies the length of the Phone column in the Employees table
by changing the data type from CHAR(15) to CHAR(20).
ALTER TABLE Employee MODIFY (Phone TO Phone CHAR(20))
|
The following
example modifies the length of the Phone column in the Employees table
by changing the data type from CHAR(15) to CHAR(20), but adds the NOT
NULL keyword to require a value is entered for this column when inserting
a new row. Any existing rows that previously contained NULL values are
assigned a new value using the GIVE keyword.
ALTER TABLE Employees MODIFY (Phone TO Phone CHAR(20)
NOT NULL
GIVE '000-0000')
|
The following
example modifies the data type of the Quantity and Amount columns in the
LineItems table by changing the data type of both columns from SMALLINT
to INT.
ALTER TABLE LineItems MODIFY (Quantity TO Quantity INT,
Amount TO Amount INT)
|
RELATED COMMANDS
<
ALTER TABLE FOREIGN KEY | Contents
| ALTER TABLE PRIMARY KEY >
|
