Scenario:
You are working as SQL Server developer and you need to prepare the script to drop the Foreign Key Constraint which was created on dbo.Orders table.Solution:
Let's create dbo.Customer and dbo.Orders sample tables and include Foreign Key Constraint as part of create table by using below script.
USE YourDatabaseName GO CREATE TABLE dbo.Customer ( Customerid INT PRIMARY KEY ,FName VARCHAR(100) ,LName VARCHAR(100) ,SSN VARCHAR(10) ) CREATE TABLE dbo.Orders ( OrderId INT Identity(1, 1) ,OrderitemName VARCHAR(50) ,OrderItemAmt INT ,Customer_id INT FOREIGN KEY REFERENCES Customer(CustomerId) )
Once the tables are created and Foreign Key Constraint as well, you can use below statement to find the Foreign Key Constraint Name with table name.
USE YourDatabaseName GO Select Schema_name(Schema_id) as SchemaName, object_name(Parent_object_id) as TableName, name as ForeignKeyConstraintName from sys.foreign_keys
Find Foreign Key Constraint Name in SQL Server with Table Name |
Now we know the constraint name, we can go ahead and write our drop constraint statement.
Syntax for Drop Foreign Key Constraint on Table
Alter Table SchemaName.TableName
Drop Constraint Constraint_Name
I used below statement to drop FK__Orders__Customer__164452B1 Foreign Key Constraint.
Alter table dbo.Orders Drop Constraint FK__Orders__Customer__164452B1
If you are interested to generate scripts to drop all Foreign Key Constraints in a database, Check this link.
Video Demo : How to Drop Foreign Key Constraints in SQL Server
No comments:
Post a Comment