Foreign Keys in Microsoft SQL Server

One of the most important concepts in databases is creating relationships between database tables. These relationships provide a mechanism for linking data stored in multiple tables and retrieving it in an efficient fashion. In order to create a link between two tables, you must specify a foreign key in one table that references a column in another table.

Database Tables and Relationships

You might already know that databases are simply a series of tables, similar to what you might already use in a spreadsheet program, such as Microsoft Excel. In fact, you can even convert an Excel spreadsheet to a database. Where databases diverge from spreadsheets, however, is when it comes to building powerful relationships between tables.

Consider, for example a database used by a company to track human resources information. That database might have a table called Employees that contains the following information for each member of the company’s staff:

• Employee ID
• FirstName
• LastName
• OfficePhone
• HomePhone
• PositionID

In this example, the employee ID is a uniquely generated integer that is assigned to each employee when they are added to the database. The position ID is a job code used to reference the employee’s position in the company. In this scheme, an employee may only have one position, but multiple (or no) employees may fill each position. For example, you might have hundreds of employees with a “Cashier” position.

The database might also contain a table called Positions with the following additional information about each position:

• PositionID
• Title
• JobLevel
• SkillCategory
• Location

The Position ID field in this table is similar to the Employee ID field in the Employees table – it is a uniquely generated integer that is created when a position is added to the database.

When we go to pull a listing of employees from the database, it would be natural to request each person’s name and their title. However, this information is stored in multiple database tables, so it can only be retrieved using a JOIN query which requires an existing relationship between the tables.

When you look at the structure of the tables, the field defining the relationship is probably obvious – the Position ID field. Each employee can have only one position and that position is identified by including the Position ID from the Positions table’s corresponding entry. In addition to being the primary key for the Positions table, in this example, the Position ID field is also a foreign key from the Employees table to the Positions table. The database can then use this field to correlate information from multiple tables and ensure that any changes or additions to the database continue to enforce referential integrity.

Once you’ve identified the foreign key, you can go ahead and pull the desired information from the database using the following query:

SELECT FirstName, LastName, Title

FROM Employees INNER JOIN Positions

ON Employees.PositionID = Positions.PositionID

Creating Foreign Keys in SQL Server

Technically, you don’t need to define the relationship explicitly to be able to perform queries like the one above. However, if you do explicitly define the relationship using a foreign key constraint, the database will be able to perform some housekeeping work for you:

• When you add a new record to the Employees table, the database will ensure that the Position ID you enter is a valid primary key in the Positions table.
• If you change a Position ID in the Positions table, the database can perform the required updates to the Employees table to preserve consistency.
• The database can protect against the impact of a deletion of a position from the position table by either refusing to delete a position with corresponding employee entries or performing a cascading delete of all related employees.

Here’s how you would create the foreign key in SQL Server:

ALTER TABLE Employees

ADD FOREIGN KEY (PositionID) 

REFERENCES  Positions(PositionID)

You may also create a foreign key when you create a table by adding the clause:

FOREIGN KEY REFERENCES Positions(PositionID)

to the end of the column definition for the foreign key column.