Modify, Delete Foreign Keys in SQL Server

Here you will learn how to modify or delete foreign keys in a table.

Foreign key constraint enforces referential integrity in SQL Server database. It is used to create relationships between two tables by associating rows of one table with that of another.

Delete a Foreign Key using T-SQL

Use the ALTER TABLE DROP CONSTRAINT command to delete a foreign key constraint in an existing table.

ALTER TABLE <table_name>DROP CONSTRAINT <foreignkey_name>;

The following deletes the foreign key on the Employee table.

ALTER TABLE Employee   DROP CONSTRAINT FK_Employee_Department

Delete a Foreign Key using SSMS

To delete a foreign key using SQL Server Management Studio, open a table in the design mode by right clicking on it and select Design option.

This will open the Employee table in the design mode.

Now, right-click anywhere on the table designer and select Relationships... This will open Foreign Key Relationships dialog, as shown below.

Select a foreign key and click on the Delete button to delete it. Close the dialog box and save a table.

Modify a Foreign Key using T-SQL

To modify a foreign key constraint using T-SQL, you must first delete the existing foreign key constraint and re-create it with the new definition.

Modify a Foreign Key using SSMS

Open SSMS, Connect to the HR database. Expand the Tables folder. Expand Keys.

Right-click on the foreign key constraint to be modified and select Modify.

A Foreign Key Relationships dialog box opens. You can make changes in the following category: Name, Description, Add, Delete, Tables and Columns Specification category, Foreign Key base table, Foreign Key columns, Primary/Unique base tables, Primary/Unique Key Columns, Delete Rule, Update Rule.

Save the changes by pressing Ctrl + s.

Thus, you can delete or modify foreign keys in SQL Server.