Multiple-Column Constraint Format

Use the multiple-column constraint format to associate one or more columns with a constraint. This alternative to the single-column constraint format allows you to associate multiple columns with a constraint.

This syntax fragment is part of the CREATE TABLE statement and the OF TYPE Clause.


Multiple-Column Constraint Format

                                                     .-,------.        
                                                     V        |        
|--+-+-NOT NULL-+--+-UNIQUE--------------------+--(----column-+--)-+-->
   | '-NULL-----'  |  (1)                      |                   |   
   |               +-------DISTINCT------------+                   |   
   |               +-PRIMARY KEY---------------+                   |   
   |               |                       (2) |                   |   
   |               '-| REFERENCES Clause |-----'                   |   
   |                 .-,------.                                    |   
   |                 V        |                           (2)      |   
   +-FOREIGN KEY--(----column-+--)--| REFERENCES Clause |----------+   
   |                  (3)                                          |   
   '-| CHECK Clause |----------------------------------------------'   

    (1)                            (4)   
>--------| Constraint Definition |------------------------------|

Notes:

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

Element	Description	Restrictions	Syntax
column	Columns on which to place constraint	Not BYTE, TEXT, BLOB, CLOB	Identifier

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

It can specify no more than 16 column names.
In Informix, 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.

When you define a unique constraint (by using the UNIQUE or DISTINCT keyword), a column cannot appear in the constraint list more than once.

Using the multiple-column constraint format, you can perform these tasks:

Create data-integrity constraints for a set of one or more columns
Declare a mnemonic name for a constraint
Specify the constraint-mode option that controls the behavior of a constraint during insert, delete, and update operations.

When you use this format, you can create composite primary and foreign keys, or define check constraints that compare data in different columns.