ReviseAlgo Logo

Keys & Constraints

Primary, Foreign, and Unique Keys

Preventing duplicate values and enforcing relational bonds.

Last Updated: June 15, 2026 • 18 min read

1. Introduction

Keys are the foundation of relational database integrity. A Primary Key uniquely identifies each row. A Foreign Key enforces referential integrity between tables. A Unique Key prevents duplicate values in a column. Together, they form the integrity backbone that prevents orphaned records, duplicate entries, and broken relationships.

2. Why It Matters

  • Data integrity: Keys prevent duplicate users, orphaned orders, and dangling references — the most common source of production data bugs.
  • Query performance: Primary keys automatically create unique B-tree indexes, making lookups by ID extremely fast.
  • Cascade behavior: Foreign keys with ON DELETE CASCADE automatically clean up child records, preventing orphaned data.
  • Schema documentation: Keys serve as self-documenting constraints — any developer can understand table relationships by reading the schema.

3. Real-World Analogy

Think of a government ID system. Your Social Security Number is your primary key — unique to you, never duplicated. Your driver's license references your SSN (a foreign key). Your email is a unique key — no two citizens can register the same email. If the government deletes your record (primary key), all linked records (license, passport) are either deleted too (CASCADE) or blocked from deletion (RESTRICT).

4. How It Works

5. Internal Architecture

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Missing index on foreign key columns

PostgreSQL does NOT automatically create indexes on FK columns. Without an index, DELETE on the parent table causes a sequential scan of the child table. For large tables, this is extremely slow.

ON DELETE CASCADE surprises

CASCADE can delete far more rows than expected. Deleting a user with CASCADE might delete their orders, which deletes order_items, which updates inventory. Always verify the cascade chain before using CASCADE in production.

Interview Insight

"Why doesn't PostgreSQL auto-create indexes on FK columns?" — Because not all FK columns need indexes (small tables, rarely-joined columns). The DBA decides based on query patterns. But in practice, most FK columns benefit from an index.

9. Quick Quiz

Q1: Can a UNIQUE constraint column contain NULL values?

Answer: Yes, and multiple NULLs are allowed because NULL != NULL in SQL. A UNIQUE constraint only prevents duplicate non-NULL values. To also prevent multiple NULLs, add a NOT NULL constraint.

Q2: What's the difference between RESTRICT and NO ACTION?

Answer: In PostgreSQL, they behave identically for simple cases. The difference is that NO ACTION can be deferred (checked at transaction commit with DEFERRABLE), while RESTRICT is always checked immediately.

10. Scenario-Based Challenge

Challenge: Multi-Tenant SaaS Schema

Design a schema for a SaaS app where each tenant's data is isolated:

  1. Every table has a tenant_id FK referencing a tenants table.
  2. Use composite primary keys (tenant_id, id) for tenant isolation.
  3. Ensure ON DELETE CASCADE when a tenant is removed.
  4. Add UNIQUE constraints scoped to tenant (e.g., unique email per tenant).

11. Debugging Exercise

Why is this DELETE taking 30 seconds?

Issue:

  1. Missing index on FK column: products.category_id has no index. PostgreSQL must scan 10M rows to check if any product references category 5. Fix: CREATE INDEX idx_products_category ON products(category_id).

12. Interview Questions

Q1: What's the difference between a primary key and a unique key?

A: A table can have only one primary key but multiple unique keys. Primary key columns are automatically NOT NULL; unique keys allow NULLs. Primary key creates a clustered index (by default); unique key creates a non-clustered index.

Q2: Can a foreign key reference a non-primary key column?

A: Yes, a FK can reference any column with a UNIQUE constraint. Example: orders can reference users.email if email has a UNIQUE constraint. However, best practice is to reference the primary key for clarity and performance.

Q3: How do you find all FK constraints in a database?

A: Query information_schema.table_constraints joined with information_schema.key_column_usage and information_schema.referential_constraints to list all FK relationships with their actions.

13. Production Considerations

  • Always index FK columns: Run this query to find unindexed FK columns: SELECT ... FROM information_schema.key_column_usage WHERE ... AND NOT EXISTS (SELECT 1 FROM pg_indexes ...)
  • Use RESTRICT for safety: Default to ON DELETE RESTRICT for important relationships. Use CASCADE only when child records have no meaning without the parent.
  • DEFERRABLE constraints: For complex transactions that temporarily violate FKs, use DEFERRABLE INITIALLY DEFERRED to check at COMMIT time.
  • Disabling FKs during bulk loads: For initial data migration, you can SET session_replication_role = 'replica' to skip FK checks, then re-enable. Use with extreme caution.
  • Monitoring: Track constraint violation errors in application logs. A spike usually indicates a bug in data creation logic.