ALTER TABLE ADD COLUMN
Adds a new
column to a table.
SYNTAX
| table_name |
Name
of the table you are adding the column to. |
| column_definition |
New
definition for the column you want to alter. |
DESCRIPTION
The ALTER
TABLE ADD COLUMN command modifies the definition of an existing table
and adds new columns. To execute the ALTER TABLE ADD COLUMN command on
a table, you must be the table owner, have DBA security privilege, 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 add multiple columns in a single command, provided
the total number of columns in the table after executing the command does
not exceed 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 Definition
| column_name |
Name
of the new column. |
| data_type |
Data
type to use for the new column. |
| domain_name |
Name
of the domain to use for the new 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 existing column the new column will be positioned after. |
| column_name_b |
Name
of the existing column the new column will be positioned before. |
You must
specify a data type for each column. 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 unless the table is empty since any existing rows will
not contain a value for the new column, which 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 new column for any rows that already exist in the table. If you
do not provide a value using the GIVE keyword, DBMaker will insert a NULL
value into the new column for any existing rows (since columns using the
SERIAL data type cannot contain NULL values, you must use the GIVE keyword
when adding a SERIAL 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 insert 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 where you want
to insert the new column relative to an existing column. The BEFORE keyword
specifies DBMaker should insert the new column before (to the immediate
left of) the specified column. The AFTER keyword specifies DBMaker should
insert the new 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 append the column to the right side of the
table.
Adding a
new column to a table has no effect on any views or 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 adds the HireDate column with the DATE data type to the Employee
table.
ALTER TABLE Employee ADD HireDate DATE
|
The following
example adds the same HireDate column from the previous example, but adds
the NOT NULL keyword to require a value is entered for this column when
inserting a new row.
ALTER TABLE Employee ADD HireDate DATE NOT NULL
|
The following
example adds the same HireDate column from the previous example, but adds
the DEFAULT keyword to insert a default value if no value is entered.
This is the only case when you may omit a value for a column defined with
the NOT NULL keyword. In this example the built-in function NOW() is used
to insert the current date if no value is specified for this column.
ALTER TABLE Employee ADD HireDate DATE NOT NULL DEFAULT NOW()
|
The following
example adds the same HireDate column from the previous example, but adds
the CHECK keyword to specify a range of acceptable values that may be
entered in the HireDate column. The VALUE keyword represents the value
you want to enter in the column.
ALTER TABLE Employee ADD HireDate DATE NOT NULL DEFAULT NOW()
CHECK VALUE > '01/01/1995'
|
The following
example adds the same HireDate column from the previous example, but uses
the user-defined D_ValidDates domain instead of the DATE data type. The
DEFAULT and CHECK keywords are usually not required when using domains,
since domains normally include their own DEFAULT and CHECK clauses.
ALTER TABLE Employee ADD HireDate D_ValidDates NOT NULL
|
RELATED COMMANDS
<
ALTER REPLICATION DROP REPLICATE
| Contents | ALTER
TABLE DROP COLUMN >
|
