SQL DROP CONSTRAINT

In SQL, the DROP CONSTRAINT command is used to remove constraints from the columns of a table. Constraints are used to limit a column on what values it can take. We add constraints to our columns while we are creating the structure of tables in SQL and when we are actually inserting values in those tables sometimes we find that there is no further requirement for that particular constraint now and using that constraint will give an unwanted result in future so in this case we need to remove that particular constraint from the column so we use DROP CONSTRAINT. We can use DROP CONSTRAINT for removing the following constraints primary keys, unique, check, and foreign keys.

Syntax for DROP Command

Below is the syntax for several commands.

For removing Unique constraint

ALTER TABLE table_name
DROP INDEX column_name;

For removing Primary Key constraint :

ALTER TABLE table_name
DROP PRIMARY KEY;

For removing Foreign Key constraint :

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

For removing the Check constraint

ALTER TABLE table_name
DROP CHECK constraint_name;

How to DROP Constraints?

To remove constraints from columns of a table follow the given steps :

Step 1: To start with first we need to create a database and to create a database we use CREATE DATABASE command. As an example we are creating a database GeekfForGeeks.

Query:

CREATE DATABASE geeksForgeeks;

Step 2: After creating the database we now need to select or use it and for that purpose we use the USE command. So now we will select our database geeksForgeeks.

Query:

USE geeksForgeeks;

Step 3: As we have selected our database now we will create a table in our database. To create a table we use CREATE TABLE command. As an example we are creating a table Courses which will consist of all constraints. Then we will look at the structure of the table also.

Query:

 CREATE TABLE COURSES(
     COURSE_ID INT(3) PRIMARY KEY,
     COURSE_NAME VARCHAR(20),
     INSTRUCTOR VARCHAR(20) UNIQUE,
     DURATION INT CONSTRAINT DURATION_CHK CHECK (DURATION>2),
     REFERENCE_ID INT,
     CONSTRAINT FK_REFER FOREIGN KEY (REFERENCE_ID)
     REFERENCES STUDENT(STUDENT_ID));

Query:

DESC COURSES;

Step 4: Now we can proceed with removing constraints from our columns . We will now remove the unique constraint, primary key constraint, foreign key constraint and check constraint from the respective columns.

Query:

 ALTER TABLE COURSES
  DROP PRIMARY KEY;

Query:

  ALTER TABLE COURSES
  DROP INDEX INSTRUCTOR;

Query:

 ALTER TABLE COURSES
 DROP FOREIGN KEY FK_REFER;

Query:

 ALTER TABLE COURSES
 DROP CHECK DURATION_CHK;

Conclusion

In this article we have learnt about how to drop constraints from a column of a table. We can remove constraints such as Unique, primary keys, foreign keys and check. We remove the constraints when we don’t require them further as we use constraints to limit a set of values that a column can undertake.

FAQs: SQL DROP CONSTRAINT

1. Why do we need to Drop a constraint?

As constraints are used to limit a column on what values it can take, sometimes in future there is no need to limit the values so then we drop that constraint.

2. Which constraints can we remove in SQL ?

In SQL we can remove the unique, primary key, foreign key and check constraint.

3. How can we add a constraint later if we haven’t specified it while creating table in SQL ?

We can add a constraint using the Alter Table command.

Syntax:

ALTER TABLE tableName

ADD CONSTRAINT constraintName columnName;