Database foundations
Primary keys, foreign keys and relationships
Identity, references and cardinality.
Understand the concept
A primary key uniquely identifies a row and cannot contain NULL. It can contain multiple columns. The OrderItem key combines OrderId and ProductId, preventing two separate lines for the same product in one order. This model stores quantity on one line; another application may have different business rules.
A foreign key requires a referenced value to exist. SalesOrder.CustomerId points to Customer.CustomerId, so inserting an order for a nonexistent customer fails. Defining a foreign key in SQL Server does not automatically create an index on the referencing column.
Cardinality describes one-to-one, one-to-many or many-to-many relationships. A customer can have many orders, and an order can have many product lines. OrderItem resolves the many-to-many relationship between orders and products. JOIN conditions should follow these keys rather than merely matching similarly named columns.
Use keys to reject invalid relationships and predict JOIN multiplicity.
Assuming a foreign key creates an index automatically, or joining by names alone.
One topic. Three levels of depth.
SELECT CustomerId, FullName FROM lab.Customer WHERE CustomerId = 1;At most one customer is found through this primary key.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Show that one customer can have several orders.
Show the solution and explanation
SELECT CustomerId, COUNT(*) AS Orders FROM lab.SalesOrder GROUP BY CustomerId HAVING COUNT(*) > 1;Customers 1, 2, 3, 4 and 5 have more than one order.