Enterprise Edition Home | Express Edition Home | Previous Page | Next Page   SQL Statements > CREATE TABLE >

Choosing a Constraint-Mode Option (IDS)

Use the constraint-mode options to control the behavior of constraints in INSERT, DELETE, and UPDATE operations. These are the options.

Mode
Effect
DISABLED
Does not enforce the constraint during INSERT, DELETE, and UPDATE operations
ENABLED
Enforces the constraint during INSERT, DELETE, and UPDATE operations If a target row causes a violation of the constraint, the statement fails. This mode is the default.
FILTERING
Enforces the constraint during INSERT, DELETE, and UPDATE operations If a target row causes a violation of the constraint, the statement continues processing. The database server writes the row in question to the violations table associated with the target table and writes diagnostic information to the associated diagnostics table.

If you choose filtering mode, you can specify the WITHOUT ERROR or WITH ERROR options. The following list explains these options.

Error Option
Effect
WITHOUT ERROR
Does not return an integrity-violation error when a filtering-mode constraint is violated during an insert, delete, or update operation. This is the default error option.
WITH ERROR
Returns an integrity-violation error when a filtering-mode constraint is violated during an insert, delete, or update operation

To reset the constraint mode of a table, see SET Database Object Mode. For information about where the database server stores rows that violate a constraint set to FILTERING, see START VIOLATIONS TABLE.

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. 

Read syntax diagramSkip visual syntax diagramMultiple-Column Constraint Format:
 
                             .-,------.
                             V        |
|--+-+-UNIQUE----------+--(----column-+--)---------------------+-->
   | |  (1)            |                                       |
   | +--------DISTINCT-+                                       |
   | '-PRIMARY KEY-----'                                       |
   |                 .-,------.                                |
   |                 V        |                           (2)  |
   +-FOREIGN KEY--(----column-+--)--| REFERENCES Clause |------+
   |                  (3)                                      |
   '-| CHECK Clause |------------------------------------------'
 
    (1)                             (4)
>---------| Constraint Definition |-----------------------------|

Notes:
  1. Informix extension
  2. See page REFERENCES Clause
  3. See page CHECK Clause
  4. See page Constraint Definition

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

You can include a maximum of 16 columns in a constraint list. For databases where the page size is two kilobytes, the total length of the list of columns cannot exceed 380 bytes.

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:

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

See also the section Differences Between a Unique Constraint and a Unique Index.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]