Primary Key vs Foreign Key in SQL: How Tables Connect

Primary keys and foreign keys serve different purposes in relational databases. A primary key uniquely identifies each row in a table, ensuring every record has a distinct identifier. It cannot contain nulls. In contrast, a foreign key establishes a relationship between tables by linking a child table's column to a parent table's primary key or unique key. This connection enforces referential integrity and maintains data consistency.

Think of a primary key like an employee's unique worker ID, which distinguishes each individual. A foreign key acts like a department code, connecting an employee to their respective department directory. This analogy illustrates the key difference: identity versus reference. The primary key identifies a row within its table, while the foreign key references a row in another table.

Together, primary and foreign keys create parent-child relationships across tables. In PostgreSQL, a parent table must exist before a child table can reference its key column. For example, when creating departments and employees tables, the parent table (departments) is defined first with a primary key (department_id). The child table (employees) then references this key using a foreign key (department_id), enforcing that every employee belongs to an existing department.

PostgreSQL automatically indexes primary keys, making queries faster by creating a unique B-tree index. However, you must manually index foreign key columns to avoid performance issues. Without this index, updating or deleting parent rows can lead to slow sequential scans across the child table.

Referential actions for foreign keys define how child records behave when a parent row is deleted. The default behavior is ON DELETE NO ACTION, which prevents the parent deletion if child rows exist. ON DELETE RESTRICT immediately blocks the deletion, while ON DELETE CASCADE automatically deletes all referencing child rows. ON DELETE SET NULL sets the foreign key columns in child rows to NULL when the parent row is removed.

In summary, primary keys uniquely identify rows within a table, while foreign keys maintain relationships between tables, ensuring data integrity and efficient querying. Understanding these constraints is crucial for designing efficient and reliable databases.