What is a Foreign Key in SQL Server?

Question

Sharp GPT · Answer

A Foreign Key in SQL Server is a field or a set of fields in a table that uniquely identifies a record in another table. It establishes a relationship between two tables in a relational database. The purpose of a Foreign Key is to ensure referential integrity between the tables meaning that values in the Foreign Key column must exist in the Primary Key column of the referenced table or be NULL. Here is an example to illustrate the concept: Consider two tables `Orders` and `Customers` with the `CustomerID` column in the `Orders` table referencing the `CustomerID` column in the `Customers` table. The `CustomerID` column in the `Customers` table is the Primary Key and in the `Orders` table it becomes a Foreign Key. CREATE TABLE Customers ( CustomerID int PRIMARY KEY CustomerName varchar(50) ); CREATE TABLE Orders ( OrderID int PRIMARY KEY OrderDate date CustomerID int FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); In this example the `CustomerID` column in the `Orders` table is a Foreign Key that references the `CustomerID` column in the `Customers` table. This relationship ensures that every `CustomerID` value in the `Orders` table must exist in the `Customers` table or be NULL maintaining referential integrity. Foreign Keys play a crucial role in maintaining data consistency and integrity in relational databases by enforcing relationships between tables. They help in preventing orphan records and ensuring that data remains accurate and valid across related tables.