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.
Read syntax diagramSkip visual syntax diagram
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 |------------------------------|

  1. Informix® extension
  2. See REFERENCES Clause
  3. See CHECK Clause
  4. 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.

Copyright© 2019 HCL Technologies Limited