SQL Server: Enable a foreign key
Learn how to enable a foreign key in SQL Server with syntax and examples.
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.
The syntax to enable a foreign key in SQL Server (Transact-SQL) is:
ALTER TABLE table_name CHECK 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 enable.
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.