DROP CONSTRAINT

The DROP CONSTRAINT data description statement deletes the definition of a referential constraint from the dictionary. It is also a CA IDMS extension of the SQL standard.

Authorization

To issue a DROP CONSTRAINT statement, you must own or have the ALTER privilege on the referencing table in the constraint named in the statement.

Syntax

►►─── DROP CONSTRAINT constraint-name ────────────────────────────────────────►

 ►─── FROM ──┬────────────────┬── table-identifier ───────────────────────────►◄
             └─ schema-name. ─┘

Parameters

constraint-name

Specifies the name of the referential constraint being dropped. Constraint-name must identify a referential constraint defined in the dictionary.

table-identifier

Specifies the referencing table in the constraint to be dropped.

schema-name

Identifies the schema with which the table is associated.

If you do not specify a schema-name, the default value is:

The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
The schema associated with the access module used at runtime, if the statement is embedded in an application program

Usage

Referential Constraints Involving SYSTEM Tables

You cannot delete a constraint involving a table in the SYSTEM schema.

Implicitly Dropped Constraints

When you issue a DROP TABLE statement with the CASCADE parameter, CA IDMS deletes the definitions of all referential constraints in which the table being dropped is the referencing table or the referenced table.

Linked Constraints

When dropping a linked constraint, CA IDMS updates every row in the referenced and referencing tables to remove the physical links between the two tables.

Example

Dropping a Referential Constraint

The following DROP CONSTRAINT statement deletes the definition of the OFFICE_POOL_EMP constraint from the dictionary:

drop constraint office_pool_emp;

More Information

For more information about defining referential constraints, see CREATE CONSTRAINT.
For more information about implicitly dropping referential constraints, see DROP TABLE.