Table Relationships

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

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. A customer's name and address live in the customers table. Every order in the orders table simply stores the customer_id — a reference, not a copy.

Relationships are enforced by Oracle through referential integrity constraints. When a foreign key constraint is active, Oracle rejects any INSERT that references a non-existent primary key with ORA-02291, and rejects any DELETE that would leave orphaned child rows with ORA-02292. This keeps data consistent automatically, without any application code.

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

-- Oracle uses VARCHAR2 (not NVARCHAR) and NUMBER (not INT).
-- REFERENCES keyword defines the foreign key link.
-- Always name constraints -- they appear in error messages and are easier to manage.
CREATE TABLE customers (
    customer_id   NUMBER(10)      PRIMARY KEY,
    first_name    VARCHAR2(50)    NOT NULL,
    last_name     VARCHAR2(50)    NOT NULL,
    email         VARCHAR2(100)   NOT NULL,
    country       VARCHAR2(50)
);
CREATE TABLE orders (
    order_id      NUMBER(10)      PRIMARY KEY,
    customer_id   NUMBER(10)      NOT NULL,
    total_amount  NUMBER(10,2)    NOT NULL,
    order_date    DATE            NOT NULL,
    status        VARCHAR2(20)    NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)  -- Oracle enforces this on every INSERT, UPDATE, DELETE
);
-- This INSERT would fail -- customer_id 9999 does not exist in customers:
-- INSERT INTO orders VALUES (1, 9999, 49.99, DATE '2024-03-01', 'pending');
-- ORA-02291: integrity constraint violated - parent key not found

• Oracle says Table created — SQL Server says Command(s) completed successfully
• ORA-02291 names the constraint, the schema, and the reason — parent key not found
• Oracle uses VARCHAR2 not NVARCHAR, and NUMBER not INT or DECIMAL
• Always name constraints — unnamed ones get system-generated names like SYS_C0012345 which are meaningless in error messages

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 category can contain many products. One department can have many employees. The one side always holds the primary key. The many side always holds the foreign key.

-- One-to-many: one customer can have many orders.
-- || is Oracle's string concatenation operator -- SQL Server uses +
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name  AS customer_name,  -- || concatenates in Oracle
    c.country,
    COUNT(o.order_id)                   AS total_orders,
    SUM(o.total_amount)                 AS total_spent,
    MAX(o.order_date)                   AS last_order_date
FROM   dataplexa_store.customers  c
LEFT JOIN dataplexa_store.orders  o ON c.customer_id = o.customer_id  -- LEFT JOIN keeps customers with no orders
GROUP  BY c.customer_id, c.first_name, c.last_name, c.country
ORDER  BY total_spent DESC NULLS LAST;

• Anna Kovac has 0 orders — the LEFT JOIN preserved her row even though no orders matched her customer_id
• || is Oracle's string concatenation — SQL Server uses + for strings
• Oracle DATE columns display as 18-JUN-24 by default — use TO_CHAR(order_date, 'YYYY-MM-DD') for a formatted output
• NULLS LAST pushes Anna's NULL total_spent to the bottom — without it, Oracle's DESC default would place her first

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. A single foreign key column cannot model this — a column holds one value. The solution is a junction table that sits between the two tables and holds one row per combination. In DataplexaStore, order_items is the junction table between orders and products.

-- Junction table with two foreign keys -- one to each parent table.
CREATE TABLE order_items (
    order_item_id  NUMBER(10)     PRIMARY KEY,
    order_id       NUMBER(10)     NOT NULL,
    product_id     NUMBER(10)     NOT NULL,
    quantity       NUMBER(5)      NOT NULL,
    price          NUMBER(10,2)   NOT NULL,   -- price at time of purchase
    CONSTRAINT fk_items_orders
        FOREIGN KEY (order_id)   REFERENCES orders   (order_id),
    CONSTRAINT fk_items_products
        FOREIGN KEY (product_id) REFERENCES products (product_id)
);
-- Traversing the many-to-many -- two JOINs always required
SELECT
    o.order_id,
    TO_CHAR(o.order_date, 'YYYY-MM-DD')  AS order_date,
    p.product_name,
    oi.quantity,
    oi.price,
    oi.quantity * oi.price               AS line_total
FROM   dataplexa_store.orders       o
JOIN   dataplexa_store.order_items  oi ON o.order_id    = oi.order_id
JOIN   dataplexa_store.products     p  ON oi.product_id = p.product_id
WHERE  o.order_id = 1042
ORDER  BY line_total DESC;

• Two JOINs are always needed to traverse a many-to-many: orders → order_items → products
• The junction table stores attributes of the relationship itself — quantity and price belong to the combination, not to either parent
• TO_CHAR(o.order_date, 'YYYY-MM-DD') formats the Oracle DATE for a clean output — without it Oracle shows 22-JAN-24

One-to-One Relationships

A one-to-one relationship means each row in table A relates to at most one row in table B. The foreign key on the child side is also declared UNIQUE to enforce this. One-to-one splits are used to separate sensitive data from general data, or to isolate rarely-accessed columns for performance.

-- One-to-one: each customer has at most one profile.
-- UNIQUE on the FK column enforces the one-to-one constraint.
CREATE TABLE customer_profiles (
    profile_id   NUMBER(10)     PRIMARY KEY,
    customer_id  NUMBER(10)     NOT NULL UNIQUE,  -- UNIQUE turns one-to-many into one-to-one
    bio          VARCHAR2(500),
    avatar_url   VARCHAR2(255),
    join_date    DATE           NOT NULL,
    CONSTRAINT fk_profiles_customers
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
SELECT
    c.first_name || ' ' || c.last_name          AS customer_name,
    c.email,
    TO_CHAR(cp.join_date, 'YYYY-MM-DD')         AS join_date,
    NVL(cp.bio, 'No bio added')                 AS bio   -- NVL replaces NULL -- Oracle equivalent of ISNULL
FROM   dataplexa_store.customers         c
LEFT JOIN dataplexa_store.customer_profiles cp ON c.customer_id = cp.customer_id
ORDER  BY cp.join_date DESC NULLS LAST;

• The UNIQUE constraint on customer_id in customer_profiles prevents two profiles referencing the same customer
• NVL(cp.bio, 'No bio added') is Oracle's equivalent of SQL Server's ISNULL(cp.Bio, 'No bio added')
• Without the UNIQUE constraint it would become a one-to-many — multiple profiles could reference the same customer

Viewing Relationships with Oracle System Views

Oracle stores every constraint in its data dictionary. The key views are USER_CONSTRAINTS — which lists all constraints owned by the current user — and USER_CONS_COLUMNS — which maps each constraint to its specific columns. Joining these two views reveals every foreign key relationship in the schema. SQL Server uses sys.foreign_keys for the same purpose — the concept is identical, only the view names differ.

-- Query Oracle's data dictionary to see every FK relationship in the schema.
-- USER_CONSTRAINTS lists all constraints owned by the current user.
-- USER_CONS_COLUMNS maps each constraint to its column.
SELECT
    uc.constraint_name,                   -- name of the FK constraint
    uc.table_name         AS child_table, -- table holding the FK
    ucc.column_name       AS child_col,   -- FK column in the child table
    uc.r_constraint_name  AS ref_constraint,  -- name of the PK being referenced
    ucc2.table_name       AS parent_table,    -- table holding the PK
    ucc2.column_name      AS parent_col,      -- PK column in the parent table
    uc.delete_rule                            -- what happens on DELETE: NO ACTION or CASCADE
FROM   user_constraints   uc
JOIN   user_cons_columns  ucc  ON uc.constraint_name   = ucc.constraint_name
JOIN   user_constraints   uc2  ON uc.r_constraint_name = uc2.constraint_name
JOIN   user_cons_columns  ucc2 ON uc2.constraint_name  = ucc2.constraint_name
WHERE  uc.constraint_type = 'R'           -- 'R' means Referential (foreign key) in Oracle
ORDER  BY child_table, parent_table;

• constraint_type = 'R' filters for Referential constraints — Oracle's term for foreign keys
• Oracle stores DELETE behaviour as NO ACTION — this means ORA-02292 is raised if you try to delete a parent that still has children
• USER_CONSTRAINTS shows constraints for the current schema — use ALL_CONSTRAINTS to see constraints across all accessible schemas
• SQL Server equivalent is querying sys.foreign_keys — same concept, different view names and columns

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.