Creating and modifying constraints

The constraint name cannot be the same as any other constraint specified within an ALTER TABLE statement, and must be unique within the table (this includes the names of any referential integrity constraints that are defined). Existing data is checked against the new condition before the statement succeeds.

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 (this includes the names of any referential integrity constraints that are defined). Existing data is checked against the new condition before the statement succeeds.

To define unique constraints using 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 (this includes 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 using 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 using the command line, enter:

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

To modify this constraint, you would have to 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. There are different types of foreign key constraints.

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 containing the foreign key
• Statements that update or delete the parent table.

To add foreign keys using 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 would have to 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 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 only alter the column names for that table after you remove the informational constraints.

To specify informational constraints on a table using 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 should only be specified if the table data is independently known to conform to the constraint. Query results might be unpredictable if the data does not actually conform to the constraint. You can also specify if 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 is the default option. This option must only be used 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 referential integrity constraints (SQLSTATE 42613).

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