How to Drop Foreign Key Constraint in SQL Server Database - SQL Server / TSQL Tutorial Part 75


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.


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

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
    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