BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Foreign Keys

The eighteenth part of the SQL Server Programming Fundamentals tutorial examines the use of foreign key constraints. These constraints define relationships between two tables, enforcing referential integrity to avoid orphaned rows in normalised tables.

Adding a Foreign Key Using Transact-SQL

Foreign keys can be added using the T-SQL ALTER TABLE command with the ADD CONSTRAINT clause. Two further items are required. The first is the FOREIGN KEY clause followed by a comma-separated list of the parent table's primary key or unique constraint columns in parentheses. The second additional item is a REFERENCES clause, which specifies the list of foreign key columns.

To create a relationship between the ContractNumber of the Jobs and Contracts table, execute the following statement:

ALTER TABLE Jobs
ADD CONSTRAINT FK_Jobs_Contracts
FOREIGN KEY (ContractNumber)
REFERENCES Contracts (ContractNumber)

JoBS Database Foreign Keys

The remainder of the foreign key constraints can now be added to the JoBS tutorial database. Use either the graphical user interface tools or T-SQL to add the following items.

Parent TableParent KeyChild TableForeign Key
CustomerAddressesAddressIdContractsCustomerAddressId
CustomersCustomerNumberCustomerComplaintsCustomerNumber
EngineersEngineerIdCustomerComplaintsEngineerId
EngineersEngineerIdEngineerSkillsEngineerId
EngineersEngineerIdEngineerStockEngineerId
EngineersEngineerIdEngineerWorkingAreasEngineerId
EngineersEngineerIdJobsEngineerId
GeographicalAreasAreaCodeCustomerAddressesAreaCode
GeographicalAreasAreaCodeEngineerWorkingAreasAreaCode
JobsJobIdJobsFollowUpJobId
JobsJobIdUsedPartsJobId
PartsPartNumberEngineerStockPartNumber
PartsPartNumberUsedPartsPartNumber
SkillsSkillCodeEngineerSkillsSkillCode
SkillsSkillCodeRequiredSkillsSkillCode
StandardJobsStandardJobIdJobsStandardJobId
StandardJobsStandardJobIdRequiredSkillsStandardJobId
23 December 2008