Master how primary keys guarantee unique row identity and how foreign keys enforce referential integrity across parent and child tables. Explore composite keys, CASCADE/RESTRICT actions, and engine differences in PostgreSQL and MySQL.
In a relational database, rows represent real-world entities (users, products, bank accounts, invoices). Without a reliable, immutable identifier, you cannot distinguish between two people with the same name or safely update a single record without accidentally corrupting duplicates.
A primary key is a column (or combination of columns) whose values uniquely identify each row in a table. It serves as the primary access path and address of the record.
A PRIMARY KEY constraint enforces two strict mathematical rules: every value must be completely unique across the table, and no value may ever be NULL.
A table can have multiple unique columns, but it can have only one PRIMARY KEY constraint. This defines the table's canonical entity identity.
A primary key can be a single column (e.g. id BIGINT) or a composite key formed by multiple columns (e.g. order_id + product_id in an order items table).
| Property | PRIMARY KEY | UNIQUE Column |
|---|---|---|
| Allowed Count per Table | Strictly 1 per table | Multiple allowed per table (e.g. `email`, `username`, `ssn`) |
| NULL Values Permitted? | NEVER. Implicitly enforces `NOT NULL` | YES (in SQL standard / PostgreSQL, multiple rows can contain `NULL` because `NULL != NULL`) |
| Clustered Storage (MySQL InnoDB) | Defines the physical clustered B-Tree row order on disk | Secondary index pointing to the primary key value |
| Foreign Key Target | The standard, default target for foreign key references | Can be referenced by foreign keys, but requires explicit specification |
A foreign key is not merely an ID column copied from another table; it is a relational database constraint that links a column in a referencing (child) table to a primary key or unique key in a referenced (parent) table.
When inserting an order with user_id = 1, the database checks the users table index. Since row 1 exists, the insert succeeds smoothly.
If a client attempts to insert user_id = 999, the database rejects the insert with an immediate foreign key constraint error. Orphaned records are mathematically prevented!
If an administrator tries to delete user #1 while orders #1001 and #1002 exist, the database acts according to the configured referential action (CASCADE, RESTRICT, or SET NULL).
While the core SQL concepts are standardized, syntax for auto-incrementing identity keys and foreign key indexing rules differ critically between PostgreSQL and MySQL.
-- 1. Parent Table with Standard SQL IDENTITY Primary Key:
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
-- 2. Child Table with Foreign Key Constraint:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL,
total NUMERIC(10, 2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
-- Foreign Key with explicit constraint name:
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
-- ⚠️ POSTGRESQL GOTCHA: Foreign keys are NOT automatically indexed!
-- You must manually create an index on the referencing child column
-- to prevent full table scans during parent deletes or joins:
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 3. Composite Primary Key Table (e.g. Order Items):
CREATE TABLE order_items (
order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT REFERENCES products(id) ON DELETE RESTRICT,
quantity INT NOT NULL CHECK (quantity > 0),
-- Composite Primary Key: Both columns together form unique identity
PRIMARY KEY (order_id, product_id)
);-- Add Primary Key to an existing table: ALTER TABLE payments ADD CONSTRAINT pk_payments PRIMARY KEY (payment_id); -- Add Foreign Key to an existing table: ALTER TABLE payments ADD CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT;
What should happen to referencing child rows when a referenced parent row is deleted or updated? Relational databases provide 4 standard referential actions to maintain data consistency:
Automatically propagates the delete or update to the child table. Deleting user #1 deletes all orders belonging to user #1.
Immediately blocks the deletion or update of the parent row if any dependent child rows exist. Raises an error instantly.
Retains the child row but sets its foreign key column to NULL. Requires the child column to be nullable!
Similar to RESTRICT, but in PostgreSQL it can be deferred until transaction commit (DEFERRABLE).
Execute real SQL queries against an interactive relational database engine. Trigger genuine constraint violation errors (duplicate primary keys, orphaned foreign keys, null primary keys) and observe referential actions in real time.
| id (PK) | name | |
|---|---|---|
| 1 | Alice Chen | alice@example.com |
| 2 | Bob Smith | bob@example.com |
| 3 | Carlos Diaz | carlos@example.com |
| id (PK) | user_id (FK) | total |
|---|---|---|
| 1001 | 1 | $129.99 |
| 1002 | 1 | $159.98 |
| 1003 | 2 | $349.99 |
Diagnose real database errors: duplicate primary keys, null keys, orphaned foreign keys, non-unique referenced targets, type mismatches, and SET NULL contradictions. Select the scenario and pick the architectural fix.
-- Setup: INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com'); -- ❌ Buggy operation attempting duplicate PK: INSERT INTO users (id, name, email) VALUES (1, 'Bob', 'bob@example.com');
A concise side-by-side comparison illustrating the complementary roles of primary keys and foreign keys in relational modeling:
| Dimension | PRIMARY KEY | FOREIGN KEY |
|---|---|---|
| Primary Objective | Identifies rows uniquely in its own table | References an existing key in another table |
| Uniqueness Requirement | Must be strictly unique across all rows | Does NOT need to be unique (in a 1-to-many relationship, many child rows share the same parent key) |
| NULL Values Permitted? | Never. Forbids NULL values | Permitted (unless explicitly marked NOT NULL; nullable FKs represent optional relationships) |
| Cardinality per Table | Strictly 1 primary key constraint per table | A table can have multiple foreign keys linking to different tables |
| Integrity Enforced | Entity Integrity (ensures every entity has a unique identity) | Referential Integrity (ensures no orphaned references exist) |
Design a robust relational schema connecting customers to orders. Identify the primary key, foreign key, parent table, child table, and configure a safe referential action.
customers and orders. Customers place orders. If a customer account is deleted, all their pending orders should automatically be deleted with them.Every row needs a deterministic identifier. A primary key enforces UNIQUE + NOT NULL and acts as the row's physical cluster or lookup root.
Foreign keys link child rows to parent keys. They prevent orphaned records and ensure child values map to real, existing entities.
Configure CASCADE to propagate deletions automatically, or RESTRICT to block deletion of parents with active dependencies.
PostgreSQL does NOT automatically index foreign key columns. Always create explicit indexes on referencing columns to prevent full table scans.
Verify your mastery of relational database constraints, referential integrity, and engine differences across PostgreSQL and MySQL with these 7 scenario-based questions.
Test your understanding of identity constraints, foreign key referential actions, composite keys, and indexing mechanics.