Dropping parent keys and foreign keys

You can drop primary parent keys, unique parent keys, and foreign keys from an existing table.

Before you begin

Before you drop a foreign key or a parent key, consider carefully the effects on your application programs. The primary key of a table serves as a permanent, unique identifier of the occurrences of the entities it describes. Application programs often depend on that identifier. The foreign key defines a referential relationship and a delete rule. Without the key, your application programs must enforce the constraints.

Procedure

Begin general-use programming interface information.To drop a key, complete the following steps:

  1. Choose the type of key that you want to drop.
  2. Drop the key by using the ALTER TABLE statement.
    Option Description
    Dropping a foreign key When you drop a foreign key using the DROP FOREIGN KEY clause of the ALTER TABLE statement, Db2 drops the corresponding referential relationships. (You must have the ALTER privilege on the dependent table and either the ALTER or REFERENCES privilege on the parent table.) If the referential constraint references a unique key that was created implicitly, and no other relationships are dependent on that unique key, the implicit unique key is also dropped.
    Dropping a unique key When you drop a unique key using the DROP UNIQUE clause of the ALTER TABLE statement, Db2 drops all the referential relationships in which the unique key is a parent key. The dependent tables no longer have foreign keys. (You must have the ALTER privilege on any dependent tables.) The table's unique index that enforced the unique key no longer indicates that it enforces a unique key, although it is still a unique index.
    Dropping a primary key When you drop a primary key using the DROP PRIMARY KEY clause of the ALTER TABLE statement, Db2 drops all the referential relationships in which the primary key is a parent key. The dependent tables no longer have foreign keys. (You must have the ALTER privilege on any dependent tables.) The table's primary index is no longer primary, although it is still a unique index.
    End general-use programming interface information.