Primary keys and relationships

A primary key consists of one or more columns that uniquely identify each row. Primary-key values cannot be NULL. A foreign key consists of one or more columns whose non-NULL values reference a primary key or UNIQUE key, usually in another table. In a one-to-many relationship, multiple rows can reference the same parent key.

Customers and Orders

Customers.CustomerID is the primary key of Customers. Orders.OrderID is the primary key of Orders. Orders.CustomerID is the foreign-key column that references Customers.CustomerID. Each order in this dataset has one valid customer; a customer can have multiple orders.

This read-only query shows three order keys and their customer references on DDI_SQL_Training dataset version 1.0.0 using SQL Server 2019 or later. You can run it after setting up the practice database in lesson 5; Chapters 2 and 3 explain its query clauses.

SELECT TOP (3) OrderID, CustomerID, OrderStatus
FROM dbo.Orders
ORDER BY OrderID;

Order 1 refers to customer 1, Alex Bennett. The Orders table stores the customer ID, not a second copy of Alex’s name. SQL Server checks the foreign-key rule when data is inserted or changed.

Optional relationships

Orders.SalesEmployeeID is a nullable foreign key to Employees.EmployeeID. In this design, NULL means no employee is assigned to that order. By contrast, Orders.CustomerID is required and cannot be NULL.

Check your understanding