DROP CONSTRAINT Clause

Use the DROP CONSTRAINT clause to destroy an existing constraint whose name you specify.

This syntax fragment is part of the ALTER TABLE statement.

DROP CONSTRAINT Clause

DROP CONSTRAINT

( constraint )


Element	Description	Restrictions	Syntax
constraint	Constraint to be dropped	Must exist in the database	Identifier

Usage

To drop an existing constraint, specify the DROP CONSTRAINT keywords and the identifier of the constraint. To drop multiple constraints on the same table, the constraint names must be in comma-separated list that is delimited by parentheses.

The constraint that you drop can have an ENABLED, DISABLED, or FILTERING mode.

Here is an example of dropping a constraint:

ALTER TABLE manufact DROP CONSTRAINT con_name;

The following example destroys both a referential constraint and a check constraint that had been defined on the orders table:

ALTER TABLE orders DROP CONSTRAINT (con_ref, con_check);

The Informix® implementation of SQL includes no DROP CONSTRAINT statement. This clause of the ALTER TABLE statement, however, provides functionality that one might expect of the DROP CONSTRAINT statement, if that statement existed.

The DROP TABLE statement implicitly drops all constraints on the specified table when it destroys that table.

Retrieving constraint names

The DROP CONSTRAINT clause requires the identifier of the constraint. If no name was declared when the constraint was created, the database server generated the identifier of the new constraint. You can query the sysconstraints system catalog table for the name and the owner of a constraint. For example, to find the name of the constraint placed on the items table, you can issue the following statement:

SELECT constrname FROM  sysconstraints
   WHERE tabid = (SELECT tabid FROM systables
      WHERE tabname = 'items');

Dependencies between constraints

When you drop a primary-key constraint or a unique constraint that has a corresponding foreign key, any associated referential constraints are also dropped.

For example, in the stores_demo database, there is a primary-key constraint on the order_num column in the orders table. A corresponding foreign-key constraint is also defined on the order_num column in the items table. These constraints define a referential relationship between the order_num columns in both tables.

Suppose that you run the

ALTER TABLE
orders DROP CONSTRAINT

statement to drop the primary-key constraint on the order_num column in the orders table. Because this referential-integrity relationship between the two tables can no longer be enforced without the primary-ley constraint, the database server takes these actions if the ALTER TABLE statement in this example succeeds:

It destroys the specified primary-key constraint on the order_num column in the orders table.
It also destroys the corresponding referential constraint on the order_num column in the items table.
It deletes from the system catalog all references to the primary key constraint on the orders table, or to the referential constraint on the items table.

System catalog effects of dropping constraints

The database maintains information about existing constraints in these system catalog tables:

sysconstraints (all constraints)
sysobjstate (all constraints)
syschecks (check constraints)
syscoldepend (check constraints and NOT NULL constraints)
syscheckudrdep (check constraints that UDRs reference)
sysreferences (referential constraints)
sysindices (referential, primary-key, or unique constraints that have no corresponding Index entry in sysindices)

After the DROP CONSTRAINT clause successfully destroys a constraint, the database server deletes or updates at least one row in one or more of the above tables.

Data type considerations

By default, every IDSSECURITYLABEL column has an implicit NOT NULL constraint, but the DROP CONSTRAINT clause cannot reference columns of type IDSSECURITYLABEL.

You can include the DROP CONSTRAINT clause in ALTER TABLE operations on typed tables of ROW data types, but with these restrictions for table hierarchies.

For a typed table with no supertable, DROP CONSTRAINT propagates to its child tables.
For the child table of a supertable, DROP CONSTRAINT fails for inherited constraints.

For example, suppose ROW type people_t exists in the database. The following DDL statements respectively

create a typed table called MyPeople,
add a UNIQUE constraint on MyPeople called very_unique on all the fields of the ROW type column,
and create a child table called LittlePeople:

CREATE TABLE IF NOT EXISTS MyPeople OF TYPE (people_t);
ALTER TABLE MyPeople ADD CONSTRAINT UNIQUE (people_t.*)
   CONSTRAINT very_unique; 
CREATE TABLE IF NOT EXISTS LittlePeople OF TYPE (people_t) 
   UNDER MyPeople;

Because LittlePeople inherited the very_unique constraint from its parent supartable MyPeople, the following ALTER TABLE DROP CONSTRAINT statement fails:

ALTER TABLE LittlePeople 
   DROP CONSTRAINT very_unique; --cannot drop an inherited constraint

But because MyPeople is a typed table with no parent, the following statement destroys the very_unique constraint instances on both the MyPeople and the LittlePeople tables within their table hierarchy:

ALTER TABLE MyPeople DROP CONSTRAINT very_unique;

Restoring a referential constraint

For some operations, such as relocating a table to another database, you might require that a referential constraint temporarily have no effect on its table. After you complete that work without the constraint, however, the referential integrity of the database typically requires that the functionality of the constraint be restored. One possible option is this:

Use the DROP CONSTRAINT clause to destroy the constraint.
Complete the tasks that need to avoid the effects of the constraint.
Use ALTER TABLE ADD CONSTRAINT to re-create the constraint.

For a very large table that already conforms to the dropped foreign-key or check constraint, using the NOVALIDATE option to the ALTER TABLE ADD CONSTRAINT statement can avoid the substantial cost of using a full-table scan to validate the constraint while it is being re-created. This NOVALIDATE option requires using the Multi-Column Constraint Format syntax to define the constraint.

Similarly, rather than destroying a referential constraint on a large table, you can temporarily disable it, and then complete tasks that require fewer resources with the constraint disabled. To resume enforcement of the constraint, you can use the SET CONSTRAINTS option of the SET Database Object Mode statement to reset the object mode to ENABLED NOVALIDATE, or to FILTERING WITH ERROR NOVALIDATE or to FILTERING WITHOUT ERROR NOVALIDATE. In each of these constraint modes, the NOVALIDATE keyword avoids the overhead of validating the constraint while its mode is being reset.