Why to DROP CONSTRAINT?

Constraints are used on table to limit type of the data. If there is no need of constraints in further, we can delete constraints to reduce the impact on the table processing.

How to drop Constraint?

Constraints are deleted by using ALTER TABLE Statement followed by DROP CONSTRAINT command. Mostly used syntaxes to drop constraints are listed as below -

ALTER TABLE child_table_name
 DROP CONSTRAINT foreignkey_name; 

ALTER TABLE table_name
DROP CONSTRAINT primarykey_name; 

ALTER TABLE table_name
 DROP CONSTRAINT existing_compkey_name;

 ALTER TABLE table_name
DROP CONSTRAINT UK_name
UNIQUE (column_name); 

ALTER TABLE table_name
 DROP CHECK chk_name
CHECK (condition); 

Scenario – Deleting existing Primary key.

Requirement – Deleting existing pk_employee primary key on employee_details table. The query was as follows -

ALTER TABLE employee_details DROP CONSTRAINT pk_employee; 

By executing above query, we can delete existing primary key on employee_details table.

Scenario – Deleting Foreign key on existing table.

Requirement – Deleting fk_employee foreign key on existing department_details table. The query was as follows –

 ALTER TABLE department_details DROP CONSTRAINT fk_employee; 

By executing above query, we can delete foreign key on existing department table.

Scenario – Deleting existing composite key.

Requirement – Deleting existing ck_employee composite key on employee_details table.
The query was as follows -

 ALTER TABLE employee_details DROP CONSTRAINT ck_employee; 

By executing above query, we can delete existing composite key on employee_details table.

Scenario – Deleting existing unique key.

Requirement– Deleting existing uq_employee unique key on employee_details table. The query was as follows -

ALTER TABLE employee_details DROP CONSTRAINT uq_employee; 

By executing above query, we can delete existing unique key on employee_details table.

Scenario – Deleting existing check constraint.

Requirement – Deleting existing chk_dept_id on employee_details table. The query was as follows -

 ALTER TABLE employee_details DROP CHECK chk_dept_id; 

By executing above query, we can delete existing check constraint on employee_details table.