Using the BEFORE Option

The BEFORE option specifies the column before which to add the new columns. In the following example, the BEFORE option directs the database server to add the item_weight column before the total_price column:

If you do not include the BEFORE option, the database server adds the new column or list of columns to the end of the table definition by default.

DEFAULT Clause

Use the DEFAULT clause to specify value that the database server should insert in a column when an explicit value for the column is not specified.

DEFAULT Clause:
 
|--DEFAULT------------------------------------------------------>
 
>--+-literal-----------------------------------------------+----|
   +-+-NULL-+----------------------------------------------+
   | '-USER-'                                              |
   |  (1)                                                  |
   +--------CURRENT--+-----------------------------------+-+
   |                 |                              (2)  | |
   |                 '-| DATETIME Field Qualifier |------' |
   +-TODAY-------------------------------------------------+
   +-SITENAME----------------------------------------------+
   '-DBSERVERNAME------------------------------------------'

Notes:

Informix extension
See page DATETIME Field Qualifier

Element	Description	Restrictions	Syntax
literal	Literal default value for the column	Must be appropriate for the data type of the column. See Using a Literal as a Default Value.	Expression, p. Expression

You cannot specify a default value for serial columns. If the table that you are altering already has rows in it when you add a column that contains a default value, the database server inserts the default value for all pre-existing rows.

The following example adds a column to the items table. In items, the new column item_weight has a literal default value:

ALTER TABLE items 
   ADD item_weight DECIMAL (6, 2) DEFAULT 2.00 
   BEFORE total_price

In this example, each existing row in the items table has a default value of 2.00 for the item_weight column.

For more information about the options of the DEFAULT clause, refer to DEFAULT Clause.

Single-Column Constraint Format

Use the Single-Column Constraint Format to associate constraints with a specified column.

Single-Column Constraint Format:
 
|--+-----------------------------------------------------+------>
   '-NOT NULL--+---------------------------------------+-'
               |  (1)                             (2)  |
               '--------| Constraint Definition |------'
 
   .---------------------------------------------------------------------------.
   V                                                                           |
>----+-+-UNIQUE----------+--------+--+---------------------------------------+-+--|
     | |  (1)            |        |  |  (1)                             (2)  |
     | '--------DISTINCT-'        |  '--------| Constraint Definition |------'
     +-PRIMARY KEY----------------+
     |                       (3)  |
     +-| REFERENCES Clause |------+
     |                  (4)       |
     '-| CHECK Clause |-----------'

Notes:

Informix extension
See page Constraint Definition
See page REFERENCES Clause
See page CHECK Clause

You cannot specify a primary-key or unique constraint on a new column if the table contains data. In the case of a unique constraint, however, the table can contain a single row of data. When you want to add a column with a primary-key constraint, the table must be empty when you issue the ALTER TABLE statement.

The following rules apply when you place primary-key or unique constraints on existing columns:

When you place a primary-key or unique constraint on a column or set of columns, the database server creates an internal B-tree index on the constrained column or set of columns unless a user-created index was defined on the column or set of columns.
When you place a primary-key or unique constraint on a column or set of columns, and a unique index already exists on that column or set of columns, the constraint shares the index. If the existing index allows duplicates, however, the database server returns an error. You must then drop the existing index before you add the constraint.
When you place a primary-key or unique constraint on a column or set of columns, and a referential constraint already exists on that column or set of columns, the duplicate index is upgraded to UNIQUE (if possible), and the index is shared.

You cannot place a unique constraint on a BYTE or TEXT column, nor can you place referential constraints on columns of these types. A check constraint on a BYTE or TEXT column can check only for IS NULL, IS NOT NULL, or LENGTH.

When you place a referential constraint on a column or set of columns, and an index already exists on that column or set of columns, the index is upgraded to UNIQUE (if possible) and the index is shared.