Primary Key and Foreign Key

Primary Key

A primary key (PK) is one or more columns that uniquely identify each row in a table. Every row must have a unique, non-null primary key value.

Rules for primary keys:

• Values must be unique across all rows
• Values must never be NULL
• Values should be immutable (not change after creation)
• A table can have only one primary key (though it can span multiple columns)

Types of primary keys

Type	Description	Example
Surrogate key	System-generated value with no business meaning	id SERIAL, id UUID
Natural key	Real-world identifier from the domain	email, isbn, tax_id
Composite key	Multiple columns combined form the PK	(order_id, product_id)

SQL examples:

-- Surrogate key (recommended for most tables)
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL
);

-- UUID surrogate key (globally unique, good for distributed systems)
CREATE TABLE events (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  event_type TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Composite primary key (junction tables)
CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

---

Foreign Key

A foreign key (FK) is one or more columns in a table that reference the primary key of another table, establishing a relationship between the two tables and enforcing referential integrity.

Referential integrity guarantees that every FK value either matches an existing PK value in the referenced table, or is NULL.

SQL example:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total_amount NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ON DELETE and ON UPDATE rules

Rule	Behavior
RESTRICT	Prevents deletion/update of the referenced row if child rows exist
CASCADE	Automatically deletes/updates child rows when the parent is deleted/updated
SET NULL	Sets FK column to NULL when parent is deleted
SET DEFAULT	Sets FK column to its default value when parent is deleted
NO ACTION	Similar to RESTRICT; checked at end of transaction (PostgreSQL default)

-- Cascade: delete all orders when the customer is deleted
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  ...
);

-- Set NULL: keep the record but clear the FK when parent is deleted
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  ...
);

---

Surrogate vs Natural Primary Keys

This is one of the most discussed design decisions in database modeling.

	Surrogate Key	Natural Key
Source	System-generated (SERIAL, UUID)	Domain value (email, isbn)
Stability	Stable — never changes	Can change (email updates)
Privacy	No business data exposed in URL/API	Exposes business data
Join performance	Fast with integers; UUID slower	Depends on data type
Readability	Opaque (id=847)	Meaningful (isbn=978-...)
Distributed systems	UUID preferred	Natural keys can conflict

Recommendation: Use surrogate integer/UUID keys for most tables. Retain natural keys as unique constraints (not as PKs) for data integrity.

---

Creating Primary and Foreign Keys in TalkingSchema

TalkingSchema automatically generates primary keys and foreign keys based on your plain language requirements. Every relationship described becomes a FK constraint in the generated schema.

Example prompts:

"Create an orders table with a foreign key to customers. Cascade deletes."

"Add a junction table for the many-to-many relationship between students and courses."

"Change the user_id foreign key to SET NULL on delete instead of CASCADE."

In the ERD canvas, FK relationships are shown as connecting lines between tables with Crow's Foot notation indicating cardinality. You can click any relationship line to view or modify the FK constraint settings.

---

Frequently Asked Questions

Can a table have no primary key?

Technically yes in SQL, but it's strongly discouraged. Tables without primary keys: cannot have FK references pointing to them, have no natural row identity (making updates and deletes ambiguous), and perform poorly for joins. Always define a primary key.

What is the difference between a unique key and a primary key?

A primary key is a special unique key that: (1) identifies the "main" identity of a row, (2) cannot be NULL, and (3) a table can have only one. A unique constraint allows NULL values (in most databases), can span multiple columns, and a table can have many unique constraints. FKs reference primary keys by default but can reference unique keys too.

Should I use UUID or integer (SERIAL/BIGSERIAL) for primary keys?

Integer (BIGSERIAL) is faster for joins, smaller in storage, and auto-increments without coordination. UUID is better for distributed systems (multiple nodes generating IDs), external-facing APIs (no ID enumeration), and merging data across systems. The practical recommendation: use BIGSERIAL for internal tables and UUID for externally-referenced entities.