Adding and Dropping PRIMARY KEY and FOREIGN KEY

Objectives

By the end of this lesson, you should be able to:

• Add or drop a PRIMARY KEY on an existing table
• Add or drop a FOREIGN KEY on an existing table

💡 Why this matters: These are the two constraints most tied to a table’s identity and its relationships (Module 9 covers relationships in full). Being able to add or change them after a table already exists matters just as much as declaring them upfront.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from Module 3.

Adding and Dropping PRIMARY KEY

A table can only have one primary key at a time (Module 3), so replacing one means dropping the old constraint first:

ALTER TABLE employees DROP CONSTRAINT employees_pkey;
ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY (id);

This drops the existing primary key (PostgreSQL’s default name for it, covered fully next lesson) and adds one back with a custom name, still on the same id column. The table briefly has no primary key at all between the two statements, in real, careful work this is exactly the kind of change worth doing inside a transaction (Module 13), so it either fully succeeds or doesn’t happen at all.

Adding and Dropping FOREIGN KEY

ALTER TABLE employees ADD COLUMN department_id INTEGER;
ALTER TABLE employees ADD CONSTRAINT fk_employees_department
    FOREIGN KEY (department_id) REFERENCES departments(id);

This is the same relationship-enforcing constraint from Module 3, added after the fact instead of inline. Once added, it behaves identically, an UPDATE or INSERT that points department_id at a row that doesn’t exist in departments is rejected:

UPDATE employees SET department_id = 999 WHERE first_name = 'Erin';

ERROR: insert or update on table "employees" violates foreign key constraint "fk_employees_department"

Dropping it removes the enforcement, without touching the department_id column or its existing values:

ALTER TABLE employees DROP CONSTRAINT fk_employees_department;

Try It

1. Create a departments table with an auto-incrementing id and a name, and insert two departments of your choice.
2. Add a department_id column to employees, then add a foreign key constraint from employees.department_id to departments.id.
3. Try updating an employee’s department_id to a value that doesn’t exist in departments, and confirm it’s rejected.
4. Drop the foreign key constraint, and confirm the same update now succeeds.

Recap

• Replacing a PRIMARY KEY means dropping the existing one first, a table can only have one at a time.
• ALTER TABLE ... ADD CONSTRAINT name FOREIGN KEY (column) REFERENCES table(column) adds relationship enforcement to an existing table.
• Both are removed the same way as any other constraint, DROP CONSTRAINT name.

Next lesson: constraint naming, PostgreSQL’s default naming pattern, and how to name constraints yourself.