SQL Drop Constraint Explained [Practical Examples]


SQL

Reviewer: Deepak Prasad

Overview of SQL Drop Constraint

SQL constraints are the restriction enforced on single or multiple columns of a table to restrict the insertion of wrong data, these constraints help maintain the integrity, reliability, and accuracy of values stored in these columns. You can create constraints using CREATE TABLE or ALTER Table statements.

SQL drop constraint is used with ALTER TABLE statement to remove the constraints enforced on columns of a table, constraints can be UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint

 

Syntax of SQL drop constraint

ALTER TABLE <table_name>
DROP [CONSTRAINT|INDEX] <constraint_name1 , [constraint_name2,constraint_name3,..]>;

 

Here,

  • table_name: It specifies a user-define table name from which we want to drop the constraint
  • constraint_name : It specifies a name of constraint we want to remove from the table

 

Examples of SQL drop constraint

Create a table with the check and unique constraint

Below we create a table in SQL:

CREATE TABLE hospital_patient (
patient_id int NOT NULL,
patient_name varchar(255) NOT NULL,
Age int,
contact varchar(20),
disease varchar(20),
doctor_id int,
PRIMARY KEY (patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id), CONSTRAINT UC_Patient UNIQUE (patient_id), CONSTRAINT check_age CHECK(age>0)
);

List out constraints on the patient table

SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'hospital_patient')

OUTPUT:

SQL Drop Constraint Explained [Practical Examples]

 

Example-1: SQL drop constraint to delete unique constraint

Write SQL query to drop SQL unique constraint from the patient table

SQL Server/Oracle

alter table hospital_patient drop constraint UC_Patient

MySQL

alter table hospital_patient drop UNIQUE UC_patient

In the above query, SQL drop constraint statement is applied on the patient table to remove UNIQUE constraint applied on patient_id column

OUTPUT:

To see the result of the above alter table statement, we need to SQL select statement to verify the list of constraints on hospital_patient table

SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'hospital_patient')

SQL Drop Constraint Explained [Practical Examples]

 

Example-2: SQL drop constraint to delete check constraint

Write SQL query to remove SQL check constraint from column age using  SQL drop constraint

SQL Server/Oracle

alter table hospital_patient drop constraint check_age

MySQL

alter table hospital_patient drop check check_age

In the above query, SQL drop constraint statement is applied with alter table to delete check constraint applied on age column of hospital_patient table

OUTPUT:

To view the result of the above alter table statement, we need to use SQL select statement to verify list of constraints on hospital_patient table

SELECT  TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'hospital_patient')

SQL Drop Constraint Explained [Practical Examples]

 

Example-3: SQL drop constraint to remove foreign key constraint

Write SQL query to remove foreign constraint applied on doctor_id column from hospi_patient table

SQL Server /Oracle

alter table hospital_patient drop constraint FK__hospital___docto__3C69FB99

MySQL

alter table hospital_patient drop FOREIGN KEY  FK__hospital___docto__3C69FB99

In the above query, SQL drop constraint is used to remove foreign key constraint which was applied on doctor_id column of hospital_patient table reference to doctor table doctor_id

OUTPUT:

To view the result of the above alter table statement, we need to use SQL select statement to verify list of constraints on hospital_patient table

SELECT  TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   (TABLE_NAME = 'hospital_patient')

SQL Drop Constraint Explained [Practical Examples]

 

Example-4: SQL drop constraint to remove primary key constraint

Example 4: Write SQL query to drop primary key constraint from hospital_patient table

SQL Server /Oracle

alter table hospital_patient drop constraint PK__hospital__4D5CE476398D8EEE

MySQL

alter table hospital_patient drop PRIMARY KEY PK__hospital__4D5CE476398D8EEE

In the above query, SQL drop constraint is used with sql alter table statement to drop primary key constraint on patient_id column of hospital_patient table

OUTPUT:

To view the result of the above alter table statement, we need to use SQL select statement to verify list of constraints on hospital_patient table

SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  (TABLE_NAME = 'hospital_patient')

SQL Drop Constraint Explained [Practical Examples]

 

SQL drop constraint to drop more than one constraint

List all constraints of  _patient table

SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  (TABLE_NAME = '_patient')

SQL Drop Constraint Explained [Practical Examples]

Example 5: Write SQL query to drop check constraint applied on age column, foreign key constraint applied on doctor_id column, and primary key constraint applied on patient_id column of _patient table

SQL Server /Oracle

alter table _patient drop constraint ch_age, FK___patient__doctor__4CA06362,PK___patient__4D5CE47649C3F6B7

MySQL

alter table _patient drop check ch_age, FOREIGN KEY FK___patient__doctor__4CA06362, PRIMARY KEY PK___patient__4D5CE47649C3F6B7

In the above query, SQL drop constraint is used with SQL alter table to drop check, foreign key and primary key constraint applied on age, doctor_id and patient_id column respectively

OUTPUT:

To view the result of the above alter table statement, we need to use SQL select statement to verify list of constraints on _patient table

SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = '_patient')

SQL Drop Constraint Explained [Practical Examples]

 

Summary

In this article on SQL drop constraint, we have discussed an overview of SQL drop constraint, the syntax to write SQL drop constraint with alter table statement, explained syntax arguments, also covered practical examples to drop check constraint, unique constraint, foreign key constraint, primary key constraint and also covered SQL drop constraint to drop all constraint of a single table

 

References

SQL constraint

 

Read More

SQL Delete check constraints

 

Related Keywords: drop constraint, drop constraint sql server, sql drop constraint, drop sql constraint, sql server drop constraint, drop foreign key sql server, alter table drop constraint, how to drop unique constraint in sql, sql alter table remove primary key, delete constraint sql, sql remove constraint, oracle sql drop constraint, drop constraint sql server

 

Falguni Thakker

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on her LinkedIn profile.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment