SQL Server: Drop a foreign key
Learn how to drop a foreign key in SQL Server with syntax and examples.
Once a foreign key has been created, you may find that you wish to drop the foreign key from the table. You can do this with the ALTER TABLE statement in SQL Server (Transact-SQL).
The syntax to drop a foreign key in SQL Server (Transact-SQL) is:
ALTER TABLE table_name
DROP CONSTRAINT fk_name;
Parameters or Arguments
The name of the table where the foreign key has been created.
The name of the foreign key that you wish to remove.
Let’s look at an example of how to drop a foreign key in SQL Server (Transact-SQL).
For example, if you had created a foreign key as follows:
CREATE TABLE products
( product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
product_id INT NOT NULL,
FOREIGN KEY (product_id)
REFERENCES products (product_id)
In this foreign key example, we’ve created our parent table as the products table. The products table has a primary key that consists of the product_id field.
Next, we’ve created a second table called inventory that will be the child table in this foreign key example. We have used the CREATE TABLE statement to create a foreign key on the inventory table called fk_inv_product_id. The foreign key establishes a relationship between the product_id column in the inventory table and the product_id column in the products table.
If we then wanted to drop the foreign key called fk_inv_product_id, we could execute the following command:
ALTER TABLE inventory
DROP CONSTRAINT fk_inv_product_id;
This foreign key example would use the ALTER TABLE statement to drop the constraint called fk_inv_product_id from the inventory table.
If you need to script out the entire constrains, below script will get all the constraint.
‘ALTER TABLE ‘ + OBJECT_SCHEMA_NAME(parent_object_id) +
‘.[‘ + OBJECT_NAME(parent_object_id) +
‘] DROP CONSTRAINT ‘ + name
WHERE referenced_object_id = object_id(‘oe_product_ext’)—Name of the table