SQL FOREIGN KEY Constraint

SQL FOREIGN KEY Constraint is used to maintain referential integrity in a database. It ensures that the values in a column of one table match the values in a primary key of another table.

Here is an example of a table Orders with a foreign key constraint on the CustomerID column:

1CREATE TABLE Orders 
2(
3  OrderID int NOT NULL,
4  CustomerID int NOT NULL,
5  OrderDate date,
6  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
7);

In this example, a foreign key constraint is created on the Orders table, where the CustomerID column references the primary key of the Customers table.

Here is an example of the data in the Orders table:

OrderID	CustomerID	OrderDate
1	10	2022-01-01
2	20	2022-02-01
3	30	2022-03-01

And here is an example of the data in the Customers table:

CustomerID	CustomerName	ContactName	Country
10	CustomerA	William Doe	USA
20	CustomerB	Jane Doe	UK
30	CustomerC	William Smith	Canada

In this example, the Orders table has a foreign key constraint on the CustomerID column that references the primary key of the Customers table. This ensures that the values in the CustomerID column of the Orders table must match the values in the primary key of the Customers table.

SQL FOREIGN KEY Constraint on ALTER TABLE:

The ALTER TABLE statement can be used to add a FOREIGN KEY constraint to an existing table. To do so, you will need to specify the name of the table you want to alter, followed by the ADD constraint keyword, the name of the constraint, and the columns that make up the foreign key. Here's an example of how to add a FOREIGN KEY constraint to a table:

1ALTER TABLE Orders
2ADD CONSTRAINT fk_Orders_Customers
3FOREIGN KEY (CustomerID)
4REFERENCES Customers (CustomerID);

In this example, the Orders table has a foreign key constraint named fk_Orders_Customers that references the Customers table. The foreign key consists of the CustomerID column in the Orders table, which must match the CustomerID column in the Customers table.

DROP a FOREIGN KEY Constraint:

You can also use the ALTER TABLE statement to drop a FOREIGN KEY constraint from a table. To do so, you will need to specify the name of the table you want to alter, followed by the DROP CONSTRAINT keyword and the name of the constraint you want to remove. Here's an example of how to drop a FOREIGN KEY constraint from a table:

1ALTER TABLE Orders
2DROP CONSTRAINT fk_Orders_Customers;

In this example, the fk_Orders_Customers foreign key constraint is being dropped from the Orders table.