Database Tables and RelationshipsYou 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
The database might also contain a table called Positions with the following additional information about each position:
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 ServerTechnically, 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.
ALTER TABLE EmployeesYou may also create a foreign key when you create a table by adding the clause:
ADD FOREIGN KEY (PositionID)
FOREIGN KEY REFERENCES Positions(PositionID)to the end of the column definition for the foreign key column.