SQL Foreign Key

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.

Example

#

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)
)

Using Foreign Keys

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
Example: This query combines the records from two tables. The JOIN matches the foreign key with the primary key in another table.
SELECT OrderNumber, TotalAmount, 
       FirstName, LastName, City, Country
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
Result:  830 records.
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

Add a Foreign Key

Syntax to add a foreign key to an existing table:

ALTER TABLE [Order] 
ADD CONSTRAINT FK_CustomerId FOREIGN KEY (CustomerId) 
    REFERENCES Customer(Id)

Drop a Foreign Key

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.


You may also like



Guides


vsn 3.1