Day 73 – SQL FOREIGN KEY Keyword
In SQL, a FOREIGN KEY is a constraint that is used to enforce referential integrity between two tables. The FOREIGN KEY constraint is used to link two tables together based on the value of a column or set of columns in each table. The purpose of this constraint is to ensure that data in one table corresponds to data in another table.
The basic syntax for creating a FOREIGN KEY constraint is as follows:
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
CONSTRAINT fk_constraint_name
FOREIGN KEY (column_name)
REFERENCES parent_table (parent_column_name)
);
Here, table_name is the name of the child table, column1, column2, etc. are the columns in the child table, fk_constraint_name is the name of the FOREIGN KEY constraint, column_name is the name of the column in the child table that references the parent table, parent_table is the name of the parent table, and parent_column_name is the name of the column in the parent table that is referenced by the child table.
For example, to create a FOREIGN KEY constraint that links the “Orders” table to the “Customers” table based on the “CustomerID” column, the SQL statement would be:
CREATE TABLE Orders
(
OrderID int NOT NULL,
CustomerID int NOT NULL,
OrderDate date,
CONSTRAINT FK_CustomersOrders
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
);
In this example, the FOREIGN KEY constraint is named “FK_CustomersOrders” and links the “Orders” table to the “Customers” table based on the “CustomerID” column. The constraint ensures that any value entered into the “CustomerID” column in the “Orders” table must correspond to a value in the “CustomerID” column of the “Customers” table.
