SQL Query to Drop Foreign Key Constraint Using ALTER Command
Here, we are going to see How to Drop a Foreign Key Constraint using ALTER Command(SQL Query) using Microsoft SQL Server.
A Foreign key is an attribute in one table which takes references from another table where it acts as the primary key in that table. Also, the column acting as a foreign key should be present in both tables.
Creating a new Database:
CREATE DATABASE Beginner;
Using the Database:
USE Beginner;
Table Definition:
We have the following emp table in our database :
CREATE TABLE emp( empno number(2) constraint pk primary key , empname varchar2(20), deptno number(2), empsal number(20));
To verify table schema use the following query:
EXEC SP_COLUMNS emp;
Output:
Adding Data to Table:
Use the below statement to add data to the emp table:
INSERT INTO emp values(1,'abc',5,20000); INSERT INTO emp values(2,'def',6,30000); INSERT INTO emp values(3,'xyz',7,40000);
Output:
Now let’s write SQL Query to Drop Foreign Key Constraint Using ALTER Command. For that, we have to create another table called “DEPT”.
Creating DEPT table
CREATE TABLE dept( deptno number(2) constraint pk2 primary key , dname varchar2(5), loc varchar2(5));
To check out the current table use the following statement:
SELECT * FROM dept;
Output:
Adding Data to Dept Table:
Use the below statement to add data to the dept table:
INSERT INTO dept values(5,'IT','hyd'); INSERT INTO dept values(6,'sales','bglr'); INSERT INTO dept values(7,'mgr','mumb');
To check out the current table use the following statement:
SELECT * FROM dept;
Output
Here we have kept the DEPTNO column as common in both EMP and DEPT tables
Adding Foreign key Constraint
ALTER TABLE emp add constraint fk foreign key(deptno) references dept(deptno); //ADDS FOREIGN KEY CONSTRAINT ON EMP TABLE
Dropping Foreign Constraint:
ALTER TABLE TABLE NAME drop constraint CONSTRAINT_NAME
Removing Foreign key Constraint from the table using ALTER:
ALTER TABLE emp drop constraint fk;
Output:
Hence, in this way, we can Drop Foreign Key Constraint Using ALTER Command
Contact Us