SQL CREATE TABLE with primary keys and foreign keys

#Why it matters

Every analytics table you query was created by someone who chose its keys, and when you learn SQL CREATE TABLE with a primary key and foreign key, you stop being the person who finds duplicate customers in a dashboard and start being the person who prevents them. SQL appears in 88% of Data Analyst and 80% of Data Engineer job descriptions we studied, and the engineer roles expect you to design tables, not only read them. Analysts meet this too: the first time you build a staging table for a messy export, the constraints you add decide whether bad rows get in silently or get stopped at the door.

#The concept

A table is a promise about what every row looks like. Constraints are how the database keeps that promise for you, on every insert and update, from every app and every script, forever.

#CREATE TABLE: columns, types, NOT NULL and DEFAULT

CREATE TABLE table_name (
    column_name data_type [constraints],
    ...
    [table-level constraints]
);

• Type limits what a value can be: integer, numeric(10,2), text, timestamp, boolean. The SQL data types and CAST: numbers, text, dates lesson covers which type to pick.

• NOT NULL says the value must exist. Add it to every column that should always have a value. Leaving it off is a decision that the column is optional.

• DEFAULT fills a value when an insert does not mention the column, for example loaded_at timestamptz NOT NULL DEFAULT now().

#PRIMARY KEY: one row per thing

A primary key is the column (or columns) that identifies a row. It means two things at once: UNIQUE and NOT NULL. A table has at most one primary key, and the database builds an index on it automatically.

Natural keys come from the real world: an invoice number, a GSTIN, a stock code. Surrogate keys are numbers the database generates with no business meaning.

The common production pattern is both: a surrogate primary key plus a UNIQUE constraint on the natural key. Each database spells the auto-numbered column differently:

product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- older code uses: product_id serial PRIMARY KEY

product_id int AUTO_INCREMENT PRIMARY KEY

product_id int IDENTITY(1,1) PRIMARY KEY

In PostgreSQL prefer GENERATED ALWAYS AS IDENTITY over serial in new code. It is the SQL standard form, and ALWAYS stops code from inserting its own ids by accident.

#FOREIGN KEY: rows must point at something real

A foreign key says "this value must exist in that other table's key". An invoice's customer_id must be a real customer. The table with the foreign key is the child; the table it points at is the parent.

What happens when someone deletes (or changes the key of) a parent row that children still point at is your choice:

NO ACTION and RESTRICT differ only in timing (NO ACTION can be deferred to the end of a transaction); both stop the delete. One more rule surprises people: a NULL foreign key is not checked. If the column allows NULL, a row with no parent at all is accepted.

#UNIQUE and CHECK

• UNIQUE stops duplicates in a column that is not the primary key, like email or stock_code. Unlike a primary key, a table can have many unique constraints, and in PostgreSQL a unique column may hold several NULLs.

• CHECK (condition) rejects any row where the condition is false: CHECK (quantity <> 0), CHECK (unit_price >= 0), CHECK (status IN ('paid', 'refunded')).

Name constraints you care about with CONSTRAINT name .... The name shows up in every error message, so unit_price_not_negative tells the person reading the log what went wrong.

#Foreign keys and indexes

The parent side of a foreign key always has an index (it is a primary key or unique). The child side may not:

• PostgreSQL and SQL Server do not index foreign key columns for you.

• MySQL InnoDB creates an index on the foreign key column automatically if none exists.

Without that index, every delete on the parent must scan the whole child table to check for children. You will see the cost in seconds in the worked example.

#Warehouses do not enforce keys

BigQuery, Snowflake and Redshift accept PRIMARY KEY and FOREIGN KEY in DDL but do not check them (Snowflake enforces only NOT NULL; BigQuery makes you write NOT ENFORCED). The optimiser may even trust them to skip work, so a wrong key there gives wrong answers. In a warehouse, uniqueness and relationships are tested by your pipeline, for example with dbt unique and relationships tests.

#CREATE TABLE AS, views and IF NOT EXISTS

• CREATE TABLE new AS SELECT ... copies the result once. It copies columns and data but no primary key, foreign keys, CHECKs or defaults.

• CREATE VIEW name AS SELECT ... stores the query, not the data, so it is always current. Views get their own lesson in the relational design module.

• CREATE TABLE IF NOT EXISTS skips creation when the name is taken, and it does not check that the existing table has the same columns.

#ALTER TABLE and DROP TABLE

ALTER TABLE changes a live table: ADD COLUMN, DROP COLUMN, RENAME COLUMN, ADD CONSTRAINT, DROP CONSTRAINT. Adding a constraint checks every existing row, which can fail on old data and can lock a big table while it scans.

DROP TABLE deletes the table and all its data, with no undo outside a transaction. Before dropping anything in a shared database, take a copy (pg_dump -t table_name or CREATE TABLE backup_x AS SELECT * FROM x) and check who uses it.

#Naming

• snake_case, lowercase: invoice_lines, customer_id. In PostgreSQL unquoted names are folded to lowercase, so CustomerID becomes customerid.

• Avoid reserved words such as order, user, group, table. They only work in quotes ("order"), and every query after that needs the quotes too. Use orders, app_users.

• Pick plural or singular table names and stick to it; name keys <thing>_id.

#Worked example: Online Retail tables in PostgreSQL

We will turn the UCI Online Retail export (541,909 invoice lines from a UK gift shop, December 2010 to December 2011, licensed CC BY 4.0) into four related tables. Every output below came from a real run on PostgreSQL 16.

Download the CSV from the Spark Definitive Guide repository and open psql in the same folder.

#Step 1: load everything as text into a staging table

CREATE TABLE raw_retail (
    invoice_no   text,
    stock_code   text,
    description  text,
    quantity     text,
    invoice_date text,
    unit_price   text,
    customer_id  text,
    country      text
);
\copy raw_retail FROM 'online-retail-dataset.csv' WITH (FORMAT csv, HEADER true)
SELECT count(*) AS rows_loaded FROM raw_retail;

 rows_loaded
-------------
      541909

A staging table with all text columns and no constraints never rejects a row, so the load always works and you can inspect problems with SQL. The real tables come next.

#Step 2: customers, and a primary key that catches a data problem

CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    country     text    NOT NULL
);

INSERT INTO customers (customer_id, country)
SELECT DISTINCT customer_id::integer, country
FROM raw_retail
WHERE customer_id IS NOT NULL;

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (customer_id)=(12417) already exists.

The primary key just found a fact about the data: some customers appear with two countries. Eight of them, it turns out. Without the key, the table would hold 4,380 "customers" for 4,372 real people and every count by country would be quietly wrong. The fix is a rule: keep the country from each customer's latest invoice.

INSERT INTO customers (customer_id, country)
SELECT DISTINCT ON (customer_id::integer)
       customer_id::integer, country
FROM raw_retail
WHERE customer_id IS NOT NULL
ORDER BY customer_id::integer,
         to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI') DESC;

SELECT count(*) AS customers FROM customers;

 customers
-----------
      4372

#Step 3: products with a surrogate key and a unique natural key

CREATE TABLE products (
    product_id  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    stock_code  text NOT NULL UNIQUE,
    description text
);

INSERT INTO products (stock_code, description)
SELECT stock_code, max(description)
FROM raw_retail
GROUP BY stock_code
ORDER BY stock_code;

SELECT count(*) AS products, max(product_id) AS highest_id FROM products;

 products | highest_id
----------+------------
     4070 |       4070

description allows NULL on purpose: 1,454 raw lines have no description. Now test the constraints:

INSERT INTO products (stock_code, description) VALUES ('85123A', 'DUPLICATE TEST');
INSERT INTO products (product_id, stock_code) VALUES (1, 'NEW1');
INSERT INTO products (stock_code, description) VALUES ('TEST1', 'A real new product')
RETURNING product_id;

ERROR:  duplicate key value violates unique constraint "products_stock_code_key"
DETAIL:  Key (stock_code)=(85123A) already exists.
ERROR:  cannot insert a non-DEFAULT value into column "product_id"
DETAIL:  Column "product_id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
 product_id
------------
       4072

Three lessons in one output. UNIQUE on the natural key blocks a second product with stock code 85123A. GENERATED ALWAYS blocks hand-picked ids. And the new product got 4072, not 4071: the failed duplicate insert had already taken 4071 from the sequence, and sequences never give numbers back. Identity columns have gaps. Never use them to count rows or as invoice numbers that must be continuous.

#Step 4: invoices with a foreign key and a default

CREATE TABLE invoices (
    invoice_no  text        PRIMARY KEY,
    customer_id integer     REFERENCES customers (customer_id) ON DELETE RESTRICT,
    invoiced_at timestamp   NOT NULL,
    country     text        NOT NULL,
    loaded_at   timestamptz NOT NULL DEFAULT now()
);

INSERT INTO invoices (invoice_no, customer_id, invoiced_at, country)
SELECT invoice_no,
       min(customer_id)::integer,
       min(to_timestamp(invoice_date, 'MM/DD/YYYY HH24:MI')),
       min(country)
FROM raw_retail
GROUP BY invoice_no;

SELECT count(*) AS invoices,
       count(*) FILTER (WHERE customer_id IS NULL) AS guest_invoices
FROM invoices;

 invoices | guest_invoices
----------+----------------
    25900 |           3710

The invoice number is a good natural key: it already identifies an invoice and nobody reuses it. customer_id allows NULL because 3,710 invoices were guest checkouts. Now the foreign key at work:

INSERT INTO invoices (invoice_no, customer_id, invoiced_at, country)
VALUES ('999001', 99999, '2011-12-10 09:00', 'India');

INSERT INTO invoices (invoice_no, customer_id, invoiced_at, country)
VALUES ('999002', NULL, '2011-12-10 09:05', 'India');

SELECT invoice_no, customer_id, loaded_at IS NOT NULL AS has_default
FROM invoices WHERE invoice_no = '999002';

DELETE FROM customers WHERE customer_id = 17850;

ERROR:  insert or update on table "invoices" violates foreign key constraint "invoices_customer_id_fkey"
DETAIL:  Key (customer_id)=(99999) is not present in table "customers".
 invoice_no | customer_id | has_default
------------+-------------+-------------
 999002     |             | t
ERROR:  update or delete on table "customers" violates foreign key constraint "invoices_customer_id_fkey" on table "invoices"
DETAIL:  Key (customer_id)=(17850) is still referenced from table "invoices".

Customer 99999 does not exist, so the invoice is rejected. The NULL customer passes, because NULL foreign keys are not checked. loaded_at was filled by its default. And RESTRICT refuses to delete customer 17850 while their invoices exist, which is exactly what you want for financial history.

#Step 5: invoice lines with CHECK constraints

The raw data repeats the same stock code on the same invoice 9,694 times, so (invoice_no, stock_code) cannot be the key. Each line gets a surrogate line_id.

CREATE TABLE invoice_lines (
    line_id    bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    invoice_no text          NOT NULL REFERENCES invoices (invoice_no) ON DELETE CASCADE,
    product_id integer       NOT NULL REFERENCES products (product_id),
    quantity   integer       NOT NULL CHECK (quantity <> 0),
    unit_price numeric(10,3) NOT NULL,
    CONSTRAINT unit_price_not_negative CHECK (unit_price >= 0)
);

INSERT INTO invoice_lines (invoice_no, product_id, quantity, unit_price)
SELECT r.invoice_no, p.product_id, r.quantity::integer, r.unit_price::numeric
FROM raw_retail AS r
JOIN products AS p ON p.stock_code = r.stock_code;

ERROR:  new row for relation "invoice_lines" violates check constraint "unit_price_not_negative"
DETAIL:  Failing row contains (299988, A563186, 4039, 1, -11062.060).

The CHECK found two "Adjust bad debt" accounting entries with a price of -11,062.06. They are not sales, so they stay out of this table (finance can model them separately):

INSERT INTO invoice_lines (invoice_no, product_id, quantity, unit_price)
SELECT r.invoice_no, p.product_id, r.quantity::integer, r.unit_price::numeric
FROM raw_retail AS r
JOIN products AS p ON p.stock_code = r.stock_code
WHERE r.unit_price::numeric >= 0;

SELECT count(*) AS lines FROM invoice_lines;

 lines
--------
 541907

Why numeric(10,3) and not (10,2)? Four lines have a price of 0.001. numeric(10,2) would not reject them; it would silently round them to 0.00. Profile the data before you pick the precision.

#Step 6: CASCADE versus RESTRICT

SELECT count(*) AS lines_on_536365 FROM invoice_lines WHERE invoice_no = '536365';
DELETE FROM invoices WHERE invoice_no = '536365';
SELECT count(*) AS lines_on_536365 FROM invoice_lines WHERE invoice_no = '536365';
DELETE FROM products WHERE stock_code = '85123A';

 lines_on_536365
-----------------
               7
 lines_on_536365
-----------------
               0
ERROR:  update or delete on table "products" violates foreign key constraint "invoice_lines_product_id_fkey" on table "invoice_lines"
DETAIL:  Key (product_id)=(3537) is still referenced from table "invoice_lines".

Deleting the invoice removed its 7 lines through ON DELETE CASCADE. Deleting a product that has sales failed, because that foreign key uses the default (NO ACTION).

#Step 7: the missing foreign key index

SELECT tablename, indexname
FROM pg_indexes
WHERE tablename IN ('customers', 'products', 'invoices', 'invoice_lines')
ORDER BY tablename, indexname;

   tablename   |        indexname
---------------+-------------------------
 customers     | customers_pkey
 invoice_lines | invoice_lines_pkey
 invoices      | invoices_pkey
 products      | products_pkey
 products      | products_stock_code_key

No index on invoice_lines.invoice_no, invoice_lines.product_id or invoices.customer_id. Here is what that costs when deleting 200 invoices (inside a transaction we roll back):

BEGIN;
EXPLAIN (ANALYZE, COSTS OFF)
DELETE FROM invoices
WHERE invoice_no IN (SELECT invoice_no FROM invoices ORDER BY invoice_no LIMIT 200);
ROLLBACK;

CREATE INDEX invoice_lines_invoice_no_idx ON invoice_lines (invoice_no);
CREATE INDEX invoice_lines_product_id_idx ON invoice_lines (product_id);
CREATE INDEX invoices_customer_id_idx     ON invoices (customer_id);
-- then the same BEGIN / EXPLAIN / ROLLBACK again

-- without the index
 Trigger for constraint invoice_lines_invoice_no_fkey: time=8586.916 calls=200
 Execution Time: 8595.636 ms
-- with the index
 Trigger for constraint invoice_lines_invoice_no_fkey: time=5.326 calls=200
 Execution Time: 13.191 ms

8.6 seconds became 13 milliseconds. Without the index, each of the 200 cascade checks scanned all 541,907 lines. In PostgreSQL, index your foreign key columns unless you have measured that you do not need to.

#Step 8: ALTER TABLE on data that already exists

ALTER TABLE invoice_lines
    ADD CONSTRAINT quantity_reasonable CHECK (abs(quantity) < 10000);

SELECT invoice_no, quantity
FROM invoice_lines
WHERE abs(quantity) >= 10000
ORDER BY line_id;

ERROR:  check constraint "quantity_reasonable" of relation "invoice_lines" is violated by some row
 invoice_no | quantity
------------+----------
 578841     |    12540
 581483     |    80995
 C581484    |   -80995
 541431     |    74215
 C541433    |   -74215

Old rows break the new rule, so ADD CONSTRAINT refuses. You can either clean the rows or add the constraint with NOT VALID, which skips the existing rows but checks every new one:

ALTER TABLE invoice_lines
    ADD CONSTRAINT quantity_reasonable CHECK (abs(quantity) < 10000) NOT VALID;

INSERT INTO invoice_lines (invoice_no, product_id, quantity, unit_price)
VALUES ('536366', 1, 50000, 1.00);

ERROR:  new row for relation "invoice_lines" violates check constraint "quantity_reasonable"
DETAIL:  Failing row contains (841896, 536366, 1, 50000, 1.000).

Look at the line_id in that row: 841896, far above the 541,907 lines in the table. Every failed load earlier took ids from the sequence. Gaps again.

#Step 9: CREATE TABLE AS, IF NOT EXISTS, reserved words and DROP

CREATE TABLE customers_copy AS SELECT * FROM customers;

SELECT conrelid::regclass AS table_name, conname, contype
FROM pg_constraint
WHERE conrelid IN ('customers'::regclass, 'customers_copy'::regclass);

CREATE TABLE IF NOT EXISTS customers (customer_id integer);
CREATE TABLE order (id integer);
DROP TABLE customers;

 table_name |    conname     | contype
------------+----------------+---------
 customers  | customers_pkey | p
NOTICE:  relation "customers" already exists, skipping
ERROR:  syntax error at or near "order"
LINE 1: CREATE TABLE order (id integer);
                     ^
ERROR:  cannot drop table customers because other objects depend on it
DETAIL:  constraint invoices_customer_id_fkey on table invoices depends on table customers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

• customers_copy has the data but no primary key. If you create a table with AS SELECT, add the keys yourself afterwards.

• IF NOT EXISTS skipped silently even though our "new" definition has one column instead of two.

• order is reserved. Call it orders.

• The foreign key protected customers from a DROP. Read the hint carefully: DROP ... CASCADE would remove the foreign key constraint on invoices without a second warning.

#Common mistakes

• No primary key at all. Duplicates arrive from a retried load and nobody notices until totals double. Every table gets a primary key, even staging tables you keep for more than a day.

• A surrogate key with no UNIQUE on the natural key. product_id 17 and 4071 can both be stock code 85123A, and sales get split across them. Add UNIQUE (stock_code).

• Assuming the foreign key column is indexed. In PostgreSQL it is not. Deletes and joins on the child side get slow as the table grows. Create the index with the foreign key.

• CASCADE everywhere. One DELETE FROM customers wipes years of invoices. Use CASCADE only for children that mean nothing without the parent.

• Forgetting that NULL passes a foreign key. If every child must have a parent, the column needs NOT NULL as well as REFERENCES.

• Trusting keys in a warehouse. BigQuery and Snowflake accept PRIMARY KEY and do not check it. Test uniqueness in the pipeline.

• Using an identity value as a business number. Ids have gaps by design. Invoice numbers that must be continuous need their own logic.

• Too small a numeric scale. numeric(10,2) rounds 0.001 to 0.00 without an error. Profile the data first.

• DROP TABLE without a copy. There is no recycle bin. Back up, check dependencies, then drop, ideally inside a transaction you can roll back.

#Interview angle

#Practice task

The customers.country column accepts any text, so a typo like "Untied Kingdom" would create a new country in every report. Lock it down with a lookup table.

1. Create a countries table with country_name text PRIMARY KEY and is_domestic boolean NOT NULL DEFAULT false.

2. Fill it with the distinct countries from raw_retail, then mark United Kingdom as domestic.

3. Add a foreign key from customers.country to countries, so that renaming a country updates the customers too.

4. Prove it works: inserting a customer from "Wakanda" must fail, and renaming "EIRE" to "Ireland" in countries must move EIRE's customers to "Ireland".

Done when the Wakanda insert fails with a foreign key error and the rename leaves zero customers with country "EIRE".

CREATE TABLE countries (
    country_name text PRIMARY KEY,
    is_domestic  boolean NOT NULL DEFAULT false
);

INSERT INTO countries (country_name)
SELECT DISTINCT country FROM raw_retail;

UPDATE countries SET is_domestic = true WHERE country_name = 'United Kingdom';
SELECT count(*) AS countries FROM countries;

ALTER TABLE customers
    ADD CONSTRAINT customers_country_fkey
    FOREIGN KEY (country) REFERENCES countries (country_name)
    ON UPDATE CASCADE;

INSERT INTO customers (customer_id, country) VALUES (99999, 'Wakanda');

UPDATE countries SET country_name = 'Ireland' WHERE country_name = 'EIRE';

SELECT country, count(*) AS customers
FROM customers
WHERE country IN ('EIRE', 'Ireland')
GROUP BY country;

 countries
-----------
        38
ERROR:  insert or update on table "customers" violates foreign key constraint "customers_country_fkey"
DETAIL:  Key (country)=(Wakanda) is not present in table "countries".
 country | customers
---------+-----------
 Ireland |         3

#Further reading

• PostgreSQL: Constraints: every constraint type, with the exact NULL and cascade rules.

• PostgreSQL: CREATE TABLE: identity columns, defaults and NOT VALID.

• MySQL: FOREIGN KEY constraints: how InnoDB indexes foreign keys and which actions it supports.

• BigQuery: primary and foreign keys: why warehouse keys are NOT ENFORCED and how the optimiser uses them.

• Online Retail dataset (UCI): source and licence of the data used here.