SAND CDBMS SQL Reference Guide
Constraints

Dropping a Constraint

Constraints can only be dropped using an ALTER TABLE...DROP CONSTRAINT statement that specifies the name of the constraint. Constraints are named like any other database object. User-specified constraint names, however, are optional. If the constraint is not given a name by the user when it is created, SAND CDBMS automatically generates a unique name for the constraint. It is recommended that users provide meaningful names for their constraints, as this will not only make it easier to drop constraints, but will also result in greater intelligibility of error messages issued when constraints are violated.

If a constraint was not given a name by the user who defined it (or if the given name is forgotten), the generated name will have to be determined in order to drop the constraint. The easiest way to determine the constraint name is to use an INSERT, UPDATE, or DELETE statement that violates the constraint intentionally. This will produce an error message that references the name of the constraint violated. For example:


CREATE TABLE inventory (inv_id CHAR(4), qoh INT, unit_wt SMALLINT,
    UNIQUE (inv_id, qoh, unit_wt) );
INSERT INTO inventory VALUES ("N123", 6698, 1024);
INSERT INTO inventory VALUES ("N123", 6698, 1024);


Error state 23500. Unique constraint violation - duplicate value
    Constraint: UK881970


ALTER TABLE inventory DROP CONSTRAINT UK881970 ;