Dropping constraints

You can explicitly drop a table check constraint using the ALTER TABLE statement, or implicitly drop it as the result of a DROP TABLE statement.

About this task

To drop constraints, use the ALTER TABLE statement with the DROP or DROP CONSTRAINT clauses. This allows you to BIND and continue accessing the tables that contain the affected columns. The name of all unique constraints on a table can be found in the SYSCAT.INDEXES system catalog view.

Procedure

To explicitly drop unique constraints, use the DROP UNIQUE clause of the ALTER TABLE statement.
The DROP UNIQUE clause of the ALTER TABLE statement drops the definition of the unique constraint constraint-name and all referential constraints that are dependent upon this unique constraint. The constraint-name must identify an existing unique constraint.
```
    ALTER TABLE table-name
      DROP UNIQUE constraint-name
```
Dropping this unique constraint invalidates any packages or cached dynamic SQL that used the constraint.
To drop primary key constraints, use the DROP PRIMARY KEY clause of the ALTER TABLE statement.
The DROP PRIMARY KEY clause of the ALTER TABLE statement drops the definition of the primary key and all referential constraints that are dependent upon this primary key. The table must have a primary key. To drop a primary key using the command line, enter:
```
    ALTER TABLE table-name
      DROP PRIMARY KEY 
```
To drop (table) check constraints, use the DROP CHECK clause of the ALTER TABLE statement.
When you drop a check constraint, all packages and cached dynamic statements with INSERT or UPDATE dependencies on the table are invalidated. The name of all check constraints on a table can be found in the SYSCAT.CHECKS catalog view. Before attempting to drop a table check constraint having a system-generated name, look for the name in the SYSCAT.CHECKS catalog view.
The following statement drops the check constraint constraint-name. The constraint-name must identify an existing check constraint defined on the table. To drop a table check constraint using the command line:
```
    ALTER TABLE table_name
      DROP CHECK check_constraint_name
```
Alternatively, you can use the ALTER TABLE statement with the DROP CONSTRAINT option.
To drop foreign key (referential) constraints, use the DROP CONSTRAINT clause of the ALTER TABLE statement.
The DROP CONSTRAINT clause of the ALTER TABLE statement drops the constraint constraint-name. The constraint-name must identify an existing foreign key constraint, primary key, or unique constraint defined on the table. To drop foreign keys using the command line, enter:
```
    ALTER TABLE table-name
      DROP FOREIGN KEY foreign_key_name 
```
When a foreign key constraint is dropped, packages or cached dynamic statements containing the following might be marked as invalid:
- Statements that insert or update the table containing the foreign key
- Statements that update or delete the parent table.

Examples

The following examples use the DROP PRIMARY KEY and DROP FOREIGN KEY clauses in the ALTER TABLE statement to drop primary keys and foreign keys on a table:

    ALTER TABLE EMP_ACT      
      DROP PRIMARY KEY      
      DROP FOREIGN KEY ACT_EMP_REF      
      DROP FOREIGN KEY ACT_PROJ_REF    
    ALTER TABLE PROJECT      
      DROP PRIMARY KEY