Relationships and foreign keys

An order in BuildMart is not one row. Who placed it lives in Customers, when and its status in Orders, what was bought in OrderItems, and what those products are in Products. Each fact is stored once, in the table it belongs to, and keys link the pieces back together.

Following the keys by hand

Without joins, putting order 7 back together takes four queries. Each one takes a value from the previous result and looks it up in the next table:

1SELECT OrderId, CustomerId, OrderDate, Status FROM dbo.Orders WHERE OrderId = 7;2SELECT CustomerId, FirstName, LastName, City FROM dbo.Customers WHERE CustomerId = 4;3SELECT OrderId, ProductId, Quantity, UnitPrice FROM dbo.OrderItems WHERE OrderId = 7;4SELECT ProductId, Name, Price FROM dbo.Products WHERE ProductId IN (4, 8);

OrderId  CustomerId  OrderDate   Status
-------  ----------  ----------  -------
      7           4  2024-05-05  Shipped

CustomerId  FirstName  LastName  City
----------  ---------  --------  --------
         4  Dev        Singh     Bradford

OrderId  ProductId  Quantity  UnitPrice
-------  ---------  --------  ---------
      7          4        20       7.00
      7          8         5      11.90

ProductId  Name               Price
---------  -----------------  -----
        4  Cement 25 kg        7.20
        8  Wood screws (200)  11.90

You had to read the 4 and the 4, 8 off the screen and type them into the next query. A join does those lookups inside one query, which is the rest of this module.

Three kinds of relationship

One-to-many is by far the most common. The foreign key always goes on the “many” side: an order holds its CustomerId, because a customer cannot hold a list of order numbers in one column.

Many-to-many needs a table in the middle. An order contains many products and a product appears on many orders, so each pair gets a row in OrderItems. Its composite primary key (OrderId, ProductId) stops the same product being listed twice on one order, and the pair is a good place for facts that belong to it, such as Quantity and UnitPrice.

A one-to-one relationship

Login details are only needed by the website, so they can live in their own table. Making CustomerId both the primary key and a foreign key allows at most one login per customer:

1CREATE TABLE dbo.CustomerLogins (2    CustomerId int          NOT NULL3        CONSTRAINT PK_CustomerLogins PRIMARY KEY4        CONSTRAINT FK_CustomerLogins_Customers REFERENCES dbo.Customers (CustomerId),5    UserName   nvarchar(40) NOT NULL UNIQUE,6    LastLogin  datetime2(0) NULL7);8 9INSERT INTO dbo.CustomerLogins (CustomerId, UserName) VALUES (1, N'asha.p');10INSERT INTO dbo.CustomerLogins (CustomerId, UserName) VALUES (1, N'asha.second');

Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK_CustomerLogins'. Cannot insert duplicate key in object 'dbo.CustomerLogins'. The duplicate key value is (1).

Natural or surrogate keys?

A natural key is a real-world value that is already unique, such as an email address or a product barcode. A surrogate key is a number made up by the database, such as an IDENTITY column. BuildMart uses surrogate keys everywhere, and that is the usual choice:

• Natural values change: people change email address, and every foreign key pointing at the old one would have to change too. A surrogate never needs to.
• A small int makes foreign keys and indexes smaller and faster than a long text value.
• Keep the natural value unique anyway with a UNIQUE constraint, as CustomerLogins does with UserName.

Listing the relationships of a database

SQL Server keeps every foreign key in its catalog views, so you can list the relationships of a database you have never seen before:

1SELECT OBJECT_NAME(parent_object_id) AS ChildTable,2       COL_NAME(parent_object_id, parent_column_id) AS ForeignKey,3       OBJECT_NAME(referenced_object_id) AS ParentTable,4       COL_NAME(referenced_object_id, referenced_column_id) AS PointsAt5FROM sys.foreign_key_columns6ORDER BY ChildTable, ForeignKey;

ChildTable  ForeignKey  ParentTable  PointsAt
----------  ----------  -----------  ----------
OrderItems  OrderId     Orders       OrderId
OrderItems  ProductId   Products     ProductId
Orders      CustomerId  Customers    CustomerId
Products    CategoryId  Categories   CategoryId

1SELECT CustomerId FROM dbo.Orders WHERE OrderId = 9;2SELECT FirstName, LastName FROM dbo.Customers WHERE CustomerId = 2;3SELECT ProductId, Quantity FROM dbo.OrderItems WHERE OrderId = 9;4SELECT Name FROM dbo.Products WHERE ProductId = 9;

CustomerId
----------
         2

FirstName  LastName
---------  --------
Ben        Carter

ProductId  Quantity
---------  --------
        9         4

Name
----------------
Wall plugs (100)