SQL Server: Enable a foreign key

Learn how to enable a foreign key in SQL Server with syntax and examples.

Description

You may encounter a foreign key in SQL Server (Transact-SQL) that has been disabled. You can enable the foreign key using the ALTER TABLE statement.

Syntax

The syntax to enable a foreign key in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
CHECK CONSTRAINT fk_name;

Parameters or Arguments

table_name

The name of the table where the foreign key has been created.

fk_name

The name of the foreign key that you wish to enable.

Example

Let's look at an example of how to enable a foreign key in SQL Server (Transact-SQL) using the ALTER TABLE statement.

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,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    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 enable the foreign key, we could execute the following command:

ALTER TABLE inventory
CHECK CONSTRAINT fk_inv_product_id;

This foreign key example would use the ALTER TABLE statement to enable the constraint called fk_inv_product_id on the inventory table.