Constraint Definition

Use the Constraint Definition segment of the ALTER TABLE statement to declare the name of a constraint, and to set its mode to DISABLED or ENABLED, or for tables that have violations tables, two FILTERING modes.

For enabled or filtering foreign-key or check constraints that the ALTER TABLE ADD CONSTRAINT statements can create, the NOVALIDATE mode can prevent the database server from checking every row of the table for compliance with the enabled constraint while the ALTER TABLE statement is creating the constraint.

Both the Single-Column Constraint Format format and the Multiple-Column Constraint Format support the following syntax for defining constraints:

Constraint Definition
|--CONSTRAINT--constraint--+-------------------------------------------------------------+-+-------------------+-->   
                           | (1)                                                         | | (3)               |
                           '-{KEEP|DROP} ANY REFERENCING FOREIGN KEY---------------------' '-ON DELETE CASCADE-'  
                           | (1)                                                         | 
                           '-{KEEP|DROP} ANY CHECK CONSTRAINT----------------------------' 
                           | (1)                                                         | 
                           '-{KEEP|DROP} ANY REFERENCING FOREIGN KEY OR CHECK CONSTRAINT-' 
                           | (1)                                                         | 
                           '-{KEEP|DROP} ANY CHECK CONSTRAINT OR REFERENCING FOREIGN KEY-'
                           | (1)                                                         | 
                           '-KEEP ANY REFERENCING FOREIGN KEY DROP ANY CHECK CONSTRAINT--' 
                           | (1)                                                         | 
                           '-DROP ANY REFERENCING FOREIGN KEY KEEP ANY CHECK CONSTRAINT--'
                           | (1)                                                         |
                           '-KEEP ANY CHECK CONSTRAINT DROP ANY REFERENCING FOREIGN KEY--' 
                           | (1)                                                         | 
                           '-DROP ANY CHECK CONSTRAINT KEEP ANY REFERENCING FOREIGN KEY--' 

>--+----------------------------------------------------------------+--|
   '-+-DISABLED---------------------------------------------------+-'   
     | .-ENABLED--------------------------.                       |     
     '-+----------------------------------+--+------------------+-'     
       |                              (2) |  |  (3) (4)           |       
       '-FILTERING--+-WITHOUT ERROR-+-----'  '-------NOVALIDATE-'       
                    '-WITH ERROR----'                           
 

1. Valid for PRIMARY KEY constraints only
2. See Filtering Modes
3. Valid for FOREIGN KEY constraints only
4. Valid for CHECK constraints only

Element	Description	Restrictions	Syntax
constraint	Name declared here for the constraint	Must be unique among the names of indexes and constraints in database	Identifier

Usage

If the ALTER TABLE statement includes the Single-Column Constraint format or the Multiple-Column Constraint format, but the Constraint Definition is empty, the database server creates and enables whatever type of constraint the Single-Column Constraint or Multiple-Column Constraint format specified, assigns to the constraint a system-generated identifier and a default object state, and registers these attributes in the sysconstraints and sysobjstate system catalog tables.

If you specify no mode for the constraint, the constraint is enabled by default.

The optional ON DELETE CASCADE keywords can precede or follow the declaration of the constraint name. For referential constraints, the ON DELETE CASCADE keywords instructs the database server to delete foreign-key rows from the child tables when it deletes rows with the corresponding primary key from the parent table. For more information on the effects of these keywords on DELETE operations, see Using the ON DELETE CASCADE Option.

While creating and enabling foreign-key or check constraints that the ALTER TABLE ADD CONSTRAINT statement defines, the NOVALIDATE keyword prevents the database server from checking every row of the table for compliance with the constraint while the ALTER TABLE statement is running. For more information on the restrictions and effects of this keyword for foreign-key constraints, see Creating foreign-key constraints in NOVALIDATE modes.

Just as in the CREATE TABLE statement, you cannot define unique constraints, primary-key constraints, or referential constraints on a BYTE or TEXT column. In addition, the table cannot be a RAW table.

For more information about constraint-mode options, see Choosing a Constraint-Mode Option.