A Foreign Key is a constraint that is placed on a column.
This constraint specifies that a matching value must exist in another table.
A foreign key effectively links two tables together.
A table can have any number of foreign keys.
Creating an Order table with a foreign key referencing the Customer table.
CREATE TABLE [Order] (
Id INT IDENTITY,
OrderDate DATETIME2 NOT NULL DEFAULT GETDATE(),
OrderNumber NVARCHAR(10) NULL,
CustomerId INT REFERENCES Customer(Id) NOT NULL,
TotalAmount DECIMAL(12,2) NULL DEFAULT 0,
CONSTRAINT PK_Order PRIMARY KEY (Id)
)
Foreign keys help maintain the referential integrity of a database.
This means that primary and foreign key values must always match.
Foreign keys are also used to link tables in SQL JOIN operations.
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
SELECT OrderNumber, TotalAmount,
FirstName, LastName, City, Country
FROM [Order] O
JOIN Customer C ON O.CustomerId = C.Id
| OrderNumber | TotalAmount | FirstName | LastName | City | Country |
|---|---|---|---|---|---|
| 542378 | 440.00 | Paul | Henriot | Reims | France |
| 542379 | 1863.40 | Karin | Josephs | Münster | Germany |
| 542380 | 1813.00 | Mario | Pontes | Rio de Janeiro | Brazil |
| 542381 | 670.80 | Mary | Saveley | Lyon | France |
| 542382 | 3730.00 | Pascale | Cartrain | Charleroi | Belgium |
| 542383 | 1444.80 | Mario | Pontes | Rio de Janeiro | Brazil |
| 542384 | 625.20 | Yang | Wang | Bern | Switzerland |
 |
|||||
Syntax to add a foreign key to an existing table:
ALTER TABLE [Order]
ADD CONSTRAINT FK_CustomerId FOREIGN KEY (CustomerId)
REFERENCES Customer(Id)
Syntax to remove (drop) a foreign key from a table:
ALTER TABLE [Order]
DROP CONSTRAINT FK_CustomerId
This will only remove the constraint, not the column.
