Single-Column Constraint Format

Use the Single-Column Constraint Format to associate one or more constraints with a single column when you alter a table.

This syntax fragment is part of the ADD Column Clause and the MODIFY Clause.

(explicit id singc001) singc001 (explicit id singc002) singc002 (explicit id singc003) singc003 (explicit id singc004) singc004

Single-Column Constraint Format

{ { NULL | NOT NULL } | { UNIQUE | []DISTINCT } | PRIMARY KEY | <REFERENCES Clause>[] | <CHECK Clause>[] } [ [] <Constraint Definition>[] ]

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 on a set of columns, the database server creates an internal B-tree index on the constrained column or set of columns, and automatically calculates column statistics, equivalent to distributions created by the UPDATE STATISTICS statement in HIGH mode, unless a user-created index was already defined on the same 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 that index. If the existing index allows duplicates, however, the database server returns an error. You must then drop the existing index before you can add the constraint.
  • When you place a primary-key constraint or a unique constraint on a column or on a set of columns on which a referential constraint already exists, the existing index that enforces the constraint 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 data types. A check constraint on a BYTE or TEXT column can check only for IS NULL, IS NOT NULL, or LENGTH.

The statement fails with an error if you specify both a NOT NULL constraint and a NULL constraint on the same column. You cannot define a NULL constraint on a column whose data type is LIST, MULTISET, SET, or IDSSECURITYLABEL.

The IDSSECURITYLABEL column has an implicit NOT NULL constraint, but it cannot have explicit single-column constraints nor be part of multiple-column referential constraints or check constraints. If the constraint is on a column that stores encrypted data, Informix® cannot enforce the constraint.

Important:

You cannot use the Single-Column Constraint Format to add a new column with a foreign-key constraint in ENABLED NOVALIDATE or FILTERING WITH ERROR NOVALIDATE or FILTERING WITHOUT ERROR NOVALIDATE constraint mode. For the ALTER TABLE statement to create a new foreign-key constraint with the NOVALIDATE keyword bypassing violations-checking during the ALTER TABLE operation, you must use the ALTER TABLE ADD CONSTRAINT syntax with the Multiple-Column Constraint Format.

1 Informix® extension
2 See Constraint Definition
3 See REFERENCES Clause
4 See CHECK Clause