Table Relationships

What Is a Table Relationship?

A table relationship is a defined connection between two tables through a shared column. One table holds a primary key — a column whose value uniquely identifies every row. The other table holds a foreign key — a column that stores the primary key value of a related row in the first table. This link means data only needs to be stored once. A customer's name and address live in the Users table. Every order in the Orders table simply stores the UserId — a reference, not a copy.

Relationships are not just a design preference — they are enforced by the database engine through referential integrity constraints. When a foreign key constraint is in place, SQL Server will reject any attempt to insert a row that references a non-existent primary key, and reject any attempt to delete a primary key row that still has dependent rows pointing to it. This keeps the data honest automatically, without any application code needing to enforce it.

• A primary key uniquely identifies every row in its table — no duplicates, no NULLs allowed
• A foreign key is a column in one table that references the primary key of another table
• The table holding the primary key is called the parent (or referenced) table
• The table holding the foreign key is called the child (or referencing) table
• A foreign key column can be NULL — meaning the row has no parent yet — unless you explicitly add NOT NULL
• Referential integrity prevents orphaned rows: a child cannot exist without a valid parent

-- Relationships are defined at table creation using REFERENCES.
-- FOREIGN KEY tells SQL Server to enforce the link automatically.
CREATE TABLE Users (
    UserId      INT           PRIMARY KEY,    -- parent table: UserId is the PK
    FirstName   NVARCHAR(50)  NOT NULL,
    LastName    NVARCHAR(50)  NOT NULL,
    Email       NVARCHAR(100) NOT NULL,
    Country     NVARCHAR(50)  NULL
);
CREATE TABLE Orders (
    OrderId     INT           PRIMARY KEY,    -- child table: OrderId is its own PK
    UserId      INT           NOT NULL,       -- foreign key column -- references Users
    Total       DECIMAL(10,2) NOT NULL,
    OrderDate   DATE          NOT NULL,
    Status      NVARCHAR(20)  NOT NULL,
    CONSTRAINT fk_orders_users                -- name the constraint for easy management
        FOREIGN KEY (UserId)                  -- this column in Orders...
        REFERENCES Users (UserId)             -- ...must match a value in Users.UserId
);
-- SQL Server now enforces the link on every INSERT, UPDATE, and DELETE.
-- This INSERT would fail -- UserId 9999 does not exist in Users:
-- INSERT INTO Orders (OrderId, UserId, Total, OrderDate, Status)
-- VALUES (1, 9999, 49.99, '2024-03-01', 'pending');
-- Error: "The INSERT statement conflicted with the FOREIGN KEY constraint"

• The CONSTRAINT fk_orders_users name makes it easy to find, describe, and drop the constraint later if needed
• SQL Server enforces the relationship on every write — not just at creation time
• The failed INSERT error message names the constraint, the database, the table, and the column — all you need to diagnose the issue
• A named constraint is much easier to manage than an unnamed one — always name your foreign keys

One-to-Many — The Most Common Relationship

A one-to-many relationship means one row in the parent table can be referenced by many rows in the child table. One customer can have many orders. One product category can contain many products. One supplier can supply many items. This is by far the most common relationship type in relational databases — it models real-world hierarchies naturally and efficiently.

The "one" side always holds the primary key. The "many" side always holds the foreign key. A single customer row in Users has one UserId. Many rows in Orders can all contain that same UserId. The customer's details are stored once and shared across every order, which is the entire point of normalisation.

-- One-to-many: one User can have many Orders.
-- Querying the relationship -- how many orders does each customer have?
SELECT
    u.UserId,
    u.FirstName + ' ' + u.LastName  AS CustomerName,  -- combine name columns
    u.Country,
    COUNT(o.OrderId)                AS TotalOrders,    -- count their orders
    SUM(o.Total)                    AS TotalSpent,     -- sum their order values
    MAX(o.OrderDate)                AS LastOrderDate   -- most recent order
FROM Users  u
LEFT JOIN Orders o ON u.UserId = o.UserId  -- LEFT JOIN keeps customers with no orders
GROUP BY u.UserId, u.FirstName, u.LastName, u.Country
ORDER BY TotalSpent DESC;

• Anna Kovac has 0 orders and NULL totals — the LEFT JOIN preserved her row even though no Orders rows matched her UserId
• The GROUP BY includes all non-aggregated SELECT columns — UserId, FirstName, LastName, Country
• One customer (one UserId) maps to many orders — this is the one-to-many relationship in action
• Without the LEFT JOIN (using INNER JOIN instead), Anna Kovac would disappear from the results entirely

Many-to-Many — The Junction Table Pattern

A many-to-many relationship means rows on both sides can relate to multiple rows on the other side. One order can contain many products. One product can appear in many orders. You cannot represent this directly with a single foreign key column — a column can only hold one value. The solution is a junction table (also called a bridge or link table) that sits between the two tables and holds one row per combination. In DataplexaStore, OrderItems is the junction table between Orders and Products.

-- Many-to-many: one Order has many Products, one Product appears in many Orders.
-- OrderItems is the junction table that models the relationship.
-- It holds two foreign keys -- one to Orders, one to Products.
CREATE TABLE OrderItems (
    OrderItemId INT            PRIMARY KEY,
    OrderId     INT            NOT NULL,
    ProductId   INT            NOT NULL,
    Quantity    INT            NOT NULL,
    Price       DECIMAL(10,2)  NOT NULL,       -- price at time of purchase
    CONSTRAINT fk_orderitems_orders
        FOREIGN KEY (OrderId)   REFERENCES Orders   (OrderId),
    CONSTRAINT fk_orderitems_products
        FOREIGN KEY (ProductId) REFERENCES Products (ProductId)
);
-- Querying across the many-to-many -- what products did each order contain?
SELECT
    o.OrderId,
    o.OrderDate,
    p.Name          AS Product,
    oi.Quantity,
    oi.Price,
    oi.Quantity * oi.Price  AS LineTotal   -- value of this line item
FROM Orders     o
JOIN OrderItems oi ON o.OrderId   = oi.OrderId    -- orders to junction
JOIN Products   p  ON oi.ProductId = p.ProductId  -- junction to products
WHERE o.OrderId = 1042                            -- just one order for clarity
ORDER BY LineTotal DESC;

• Order 1042 contains four products — the junction table has four rows, each linking this order to a different product
• The same product (Wireless Mouse) could appear in hundreds of other orders — the junction table has one row per Order+Product combination
• The junction table also stores attributes of the relationship itself — Quantity and Price belong to the combination, not to either parent table alone
• Two JOINs are always needed to traverse a many-to-many: Orders → OrderItems → Products

One-to-One Relationships

A one-to-one relationship means each row in table A relates to at most one row in table B, and vice versa. This is less common than one-to-many but useful for splitting a wide table into two narrower ones — separating sensitive data (passwords, payment details) from general data, or separating rarely-accessed columns from frequently-accessed ones for performance. The foreign key on the child side is also declared UNIQUE to enforce that no two child rows can reference the same parent row.

-- One-to-one: each User has at most one UserProfile.
-- The UNIQUE constraint on the FK enforces the "at most one" part.
CREATE TABLE UserProfiles (
    ProfileId   INT            PRIMARY KEY,
    UserId      INT            NOT NULL UNIQUE,  -- UNIQUE enforces one-to-one
    Bio         NVARCHAR(500)  NULL,
    AvatarUrl   NVARCHAR(255)  NULL,
    JoinDate    DATE           NOT NULL,
    CONSTRAINT fk_profiles_users
        FOREIGN KEY (UserId) REFERENCES Users (UserId)
);
-- Querying a one-to-one -- LEFT JOIN keeps users who have no profile yet
SELECT
    u.FirstName + ' ' + u.LastName  AS CustomerName,
    u.Email,
    up.JoinDate,
    ISNULL(up.Bio, 'No bio added')  AS Bio      -- fallback for missing profiles
FROM Users        u
LEFT JOIN UserProfiles up ON u.UserId = up.UserId
ORDER BY up.JoinDate DESC;

• The UNIQUE constraint on UserId in UserProfiles prevents two profiles referencing the same user — that is what makes it one-to-one
• Priya, Tom, and Anna have no profile row yet — LEFT JOIN keeps them and ISNULL provides the fallback text
• One-to-one splits are common for sensitive data: a UserPayments table could hold card details separately from Users, with tighter permissions
• Without the UNIQUE constraint it would be a one-to-many — many profiles could reference the same user

Viewing Relationships with System Views

SQL Server stores every foreign key constraint in its system catalogue. You can query sys.foreign_keys and related views to see every relationship in a database — which tables are connected, which columns carry the keys, and what the constraints are named. This is essential when working with an unfamiliar database: understanding the relationship map before writing JOINs prevents errors and saves time.

-- Query the system catalogue to see every foreign key relationship
-- in the current database. Useful for mapping an unfamiliar schema.
SELECT
    fk.name                         AS ConstraintName,   -- the FK constraint name
    tp.name                         AS ParentTable,       -- table holding the PK
    cp.name                         AS ParentColumn,      -- the PK column
    tr.name                         AS ChildTable,        -- table holding the FK
    cr.name                         AS ChildColumn,       -- the FK column
    fk.delete_referential_action_desc AS OnDelete,        -- CASCADE, NO ACTION etc.
    fk.update_referential_action_desc AS OnUpdate         -- CASCADE, NO ACTION etc.
FROM sys.foreign_keys               fk
JOIN sys.foreign_key_columns        fkc ON fk.object_id      = fkc.constraint_object_id
JOIN sys.tables                     tp  ON fk.referenced_object_id = tp.object_id
JOIN sys.columns                    cp  ON fkc.referenced_object_id = cp.object_id
                                       AND fkc.referenced_column_id = cp.column_id
JOIN sys.tables                     tr  ON fk.parent_object_id  = tr.object_id
JOIN sys.columns                    cr  ON fkc.parent_object_id  = cr.object_id
                                       AND fkc.parent_column_id  = cr.column_id
ORDER BY ParentTable, ChildTable;

• The result shows all four relationships in DataplexaStore — parent table, parent column, child table, child column, and the referential action for deletes and updates
• NO_ACTION means SQL Server raises an error if you try to delete a parent row that still has child rows — the safest default
• CASCADE would automatically delete or update child rows when the parent changes — powerful but use with care
• This query is the fastest way to map any unfamiliar database — run it first before writing JOINs against a schema you have not seen before

Lesson Summary

Practice Questions

Quiz

Next up — INNER JOIN - How to combine rows from two tables by matching values in related columns, returning only the rows that have a match on both sides.