Creating and modifying constraints

Constraints can be added to existing tables with the ALTER TABLE statement.

About this task

Creating and modifying unique constraints

Unique constraints can be added to an existing table. The constraint name cannot be the same as any other constraint specified within the ALTER TABLE statement, and must be unique within the table. The constraint name must also be different from any of the referential integrity constraints names that are defined. Existing data is checked against the new condition before the statement succeeds.

To define unique constraints through the command line, use the ADD CONSTRAINT option of the ALTER TABLE statement. For example, the following statement adds a unique constraint to the EMPLOYEE table that represents a new way to uniquely identify employees in the table:

   ALTER TABLE EMPLOYEE
      ADD CONSTRAINT NEWID UNIQUE(EMPNO,HIREDATE)

To modify this constraint, you would have to drop it, and then re-create it.

Creating and modifying primary key constraints

A primary key constraint can be added to an existing table. The constraint name must be unique within the table, and from the names of any referential integrity constraints that are defined. Existing data is checked against the new condition before the statement succeeds.

To add primary keys through the command line, enter:

   ALTER TABLE <name>
      ADD CONSTRAINT <column_name>
      PRIMARY KEY <column_name>

An existing constraint cannot be modified. To define another column, or set of columns, as the primary key, the existing primary key definition must first be dropped, and then re-created.

Creating and modifying check constraints

When a table check constraint is added, packages and cached dynamic SQL that insert or update the table might be marked as invalid.

To add a table check constraint through the command line, enter:

   ALTER TABLE EMPLOYEE
      ADD CONSTRAINT REVENUE CHECK (SALARY + COMM > 25000)

To modify this constraint, you must drop it, and then re-create it.

Creating and modifying foreign key (referential) constraints

A foreign key is a reference to the data values in another table. Different types of foreign key constraints exist.

When a foreign key is added to a table, packages and cached dynamic SQL containing the following statements might be marked as invalid:

Statements that insert or update the table that contains the foreign key.
Statements that update or delete the parent table.

To add foreign keys through the command line, enter:

   ALTER TABLE <name>
      ADD CONSTRAINT <column_name>
       FOREIGN KEY <column_name>
       ON DELETE <action_type>
       ON UPDATE <action_type>

The following examples show the ALTER TABLE statement to add primary keys and foreign keys to a table:

   ALTER TABLE PROJECT
     ADD CONSTRAINT PROJECT_KEY
         PRIMARY KEY (PROJNO)
   ALTER TABLE EMP_ACT
     ADD CONSTRAINT ACTIVITY_KEY
         PRIMARY KEY (EMPNO, PROJNO, ACTNO)
     ADD CONSTRAINT ACT_EMP_REF
         FOREIGN KEY (EMPNO)
         REFERENCES EMPLOYEE
         ON DELETE  RESTRICT
     ADD CONSTRAINT ACT_PROJ_REF
         FOREIGN KEY (PROJNO)
         REFERENCES PROJECT
         ON DELETE CASCADE

To modify this constraint, you must drop it and then re-create it.

Creating and modifying informational constraints

To improve the performance of queries, you can add informational constraints to your tables. You add informational constraints by using the CREATE TABLE or ALTER TABLE statement when you specify the NOT ENFORCED option on the DDL. Along with the NOT ENFORCED option, you can further specify the constraint to be either TRUSTED or NOT TRUSTED.

Restriction: After you define informational constraints on a table, you can alter the column names only for that table after you remove the informational constraints.

To specify informational constraints on a table through the command line, enter one of the following commands for a new table:

   ALTER TABLE <name> <constraint attributes> NOT ENFORCED

   ALTER TABLE <name> <constraint attributes> NOT ENFORCED TRUSTED

   ALTER TABLE <name> <constraint attributes> NOT ENFORCED NOT TRUSTED

ENFORCED or NOT ENFORCED: Specifies whether the constraint is enforced by the database manager during normal operations such as insert, update, or delete.

ENFORCED cannot be specified for a functional dependency (SQLSTATE 42621).
NOT ENFORCED must be specified only if the table data is independently known to conform to the constraint. Query results might be unpredictable if the data does not conform to the constraint. You can also specify whether the NOT ENFORCED constraint is to be TRUSTED or NOT TRUSTED.
- TRUSTED: Informs the database manager that the data can be trusted to conform to the constraint. This option is the default. This option must be used only if the data is independently known to conform to the constraint.
- NOT TRUSTED: Informs the database manager that the data cannot be trusted to conform to the constraint. This option is intended for cases where the data conforms to the constraint for most rows, but it is not independently known to conform to the constraint. NOT TRUSTED can be specified only for foreign keys (SQLSTATE 42601).

To modify this constraint, you must drop it and then re-create it.