Multiple-Column Constraint Format

Use this option to assign one or more constraints to a column or to a set of columns in an existing table.

This closely resembles the syntax of the Multiple Column Constraint Format of the CREATE TABLE statement, but the optional INDEX DISABLED keywords are not valid (and return an error) in foreign key constraints that the CREATE TABLE statement defines. This syntax fragment is part of the ADD CONSTRAINT Clause.

^{(explicit id multcoc001)
multcoc001} ^{(explicit id multcoc002)
multcoc002} ^{(explicit id multcoc003)
multcoc003} ^{(explicit id multcoc004)
multcoc004} ^{(explicit id multcoc005)
multcoc005} ^{(explicit id multcoc006)
multcoc006} ^{(explicit id multcoco007)
multcoco007}

Multiple-Column Constraint Format

{ { { { ^[] | NOT NULL | NULL } | { UNIQUE | ^[]DISTINCT | PRIMARY KEY | <REFERENCES Clause>^[] } } ( ^[]column ) | <CHECK Clause>^[] <FOREIGN KEY Definition> } [ ^[] <Constraint>^[] ] | <FOREIGN KEY Definition> [ CONSTRAINTconstraint ] ^[]INDEX DISABLED^[] }

FOREIGN KEY Definition

FOREIGN KEY ( ^[]column ) <REFERENCES Clause>^[]


Element	Description	Restrictions	Syntax
column	A column on which the constraint is placed	No more than 16 columns	Identifier
constraint	The name of a disabled foreign-key constraint	Must be unique among the names of indexes and constraints in the database	Identifier

As in the CREATE TABLE statement, the Multiple-Column Constraint format for ALTER TABLE differs from the Single-Column Constraint format by requiring the FOREIGN KEY keywords before the REFERENCES clause when you specify a foreign key constraint. In addition, as its name implies, the Multiple-Column format can specify a list of columns as the scope of the new constraint, but this syntax is also valid with a single column.

For information about the INDEX DISABLED keyword option, see Using the INDEX DISABLED keywords in a foreign key definition.

A multiple-column constraint has these cardinality and size restrictions:

It can specify no more than 16 column names.
The maximum total length of the list of columns depends on the page size, according to this formula:
```
MAXLength = (((PageSize - 93)/5) -1)
```
- For a page size of 2K, the total length cannot exceed 390 bytes.
- For a page size of 16K, the total length cannot exceed 3257 bytes.

Here the slash ( / ) symbol represents integer division.

The statement fails with an error if you specify both a NOT NULL constraint and a NULL constraint on the same column, or if you define a NOT NULL constraint on a column whose default value is NULL.

You cannot define a NULL constraint on a column whose data type is LIST, MULTISET, SET, or IDSSECURITYLABEL.

If the constraint is on a set of columns that includes a column that stores encrypted data, Informix® cannot enforce the constraint. You can declare a name for the constraint and set its mode with Constraint Definition.

If the ALTER TABLE ADD CONSTRAINT statement defines more than one referential constraints on the same table, each constraint requires its own REFERENCES clause, so that options like ON DELETE CASCADE can be specified (or omitted) for each individual constraint, rather than applied to all of the constraints.

If the database server implicitly creates an index on the same non-opaque column or set of columns as the referential constraint, distribution statistics are automatically calculated on the specified column, or on the lead column of a multiple-column constraint.

These distribution statistics are equivalent to distributions created by the UPDATE STATISTICS statement in HIGH mode, and are available to the query optimizer when it designs query plans for the table on which the new constraint was created. See also the section Automatic Calculation of Distribution Statistics in the description of the CREATE INDEX statement for additional information about statistical distributions that are calculated when an index or constraint is created on an existing table.

Example of adding a multiple-column referential constraint

In the stores_demo database, the stock table contains the stock_num column as its primary key. An enabled referential constraint aa on the catalog table has columns catalog.stock_num and catalog.manu_code as its multiple-column foreign-key, referencing the stock table. The following example drops that existing foreign-key constraint, and add a new constraint called ai, in DISABLED object mode, that disables the associated system-generated index on those columns:

ALTER TABLE catalog DROP CONSTRAINT aa;

ALTER TABLE catalog ADD CONSTRAINT
   (FOREIGN KEY (stock_num, manu_code) REFERENCES stock
   CONSTRAINT ai DISABLED INDEX DISABLED);

The parentheses that delimit the definition of the new constraint are optional, the following ALTER TABLE statement is equivalent:

ALTER TABLE catalog ADD CONSTRAINT
   FOREIGN KEY (stock_num, manu_code) REFERENCES stock
   CONSTRAINT ai DISABLED INDEX DISABLED;

The first DISABLED sets the object more of the constraint. The last DISABLED sets the object mode of the index.

¹ See Using the INDEX DISABLED keywords in a foreign key definition

² Informix® extension

³ Use path no more than 16 times

⁴ See CHECK Clause

⁵ See REFERENCES Clause

⁶ See Constraint Definition

⁷ Not valid in ALTER TABLE ADD CONSTRAINT statements. See MODIFY Clause.