ADD CONSTRAINT Clause

This syntax fragment is part of the ALTER TABLE statement.

ADD CONSTRAINT Clause

Notes:

• ¹ For NULL and NOT NULL constraints, use instead the MODIFY Clause
• ² See Multiple-Column Constraint Format

For example, to add a unique constraint to the fname and lname columns of the customer table, use the following statement:

ALTER TABLE customer ADD CONSTRAINT UNIQUE (lname, fname); 

To declare a name for the constraint, change the preceding statement by adding the CONSTRAINT keyword and an identifier for the constraint:

ALTER TABLE customer 
   ADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust; 

The name must be unique among the identifiers of constraints that are defined on the same table. If you define no name for the constraint, the database server assigns to the constraint a system-defined identifier, and stores this in the sysconstraints.constrid column of the system catalog.

The new constraint is enabled by default. To add a constraint that is not enabled, you can include the DISABLED keyword after the name of the constraint:

ALTER TABLE customer 
   ADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust DISABLED; 

Before you perform subsequent DML operations in which you want the constraint to be enforced. You can use the SET Database Object Mode statement to enable the disabled constraint.

The following restrictions on the ADD CONSTRAINT clause (and on the MODIFY clause) affect constraints that the ALTER TABLE statement defines:

• When you add a constraint, the collating order must be the same as when the table was created.
• The ADD CONSTRAINT clause cannot define NULL or a NOT NULL constraints on columns of any data type. Only the MODIFY clause can define a NULL or a NOT NULL constraint on columns in existing tables.
• You cannot define primary key constraints, foreign key constraints, or unique constraints on RAW tables. You can, however, use the MODIFY clause of the ALTER TABLE statement to define a NOT NULL constraint or a NULL constraint (but not both) on a column in a RAW table. For the syntax to add a NULL or NOT NULL constraint on a column in an existing table, see MODIFY Clause.
• You cannot place a unique constraint nor referential constraints on a BYTE or TEXT column.
• A check constraint on a BYTE or TEXT column can check only for IS NULL, IS NOT NULL, or LENGTH.
• By default, every IDSSECURITYLABEL column has an implicit NOT NULL constraint. You cannot, however, use the ADD CONSTRAINT clause to reference an IDSSECURITYLABEL column in the definition of a single-column constraint, nor as part of a multiple-column referential constraint or check constraint.