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