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:
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')
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')
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')
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 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')
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')
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
Read More
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