SET/DROP DEFAULT and SET/DROP NOT NULL

Objectives

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

• Add or remove a DEFAULT on an existing column
• Add or remove a NOT NULL constraint on an existing column
• Explain why SET NOT NULL can fail on a table that already has data

💡 Why this matters: DEFAULT and NOT NULL (Module 3) aren’t fixed forever at CREATE TABLE time. Requirements change, a column that used to be optional becomes required, or vice versa.

⚠️ 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.

SET DEFAULT and DROP DEFAULT

ALTER TABLE employees ALTER COLUMN department SET DEFAULT 'Unassigned';

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Erin', 'Castillo', 'erin.castillo@example.com', 70000);

 first_name | department
------------+------------
 Erin       | Unassigned

department wasn’t mentioned in the INSERT, and picked up the new default. SET DEFAULT only affects future inserts, it doesn’t retroactively change rows already in the table. Removing it is just as direct:

ALTER TABLE employees ALTER COLUMN department DROP DEFAULT;

Future inserts that skip department now store NULL again, exactly like before SET DEFAULT was applied.

SET NOT NULL and DROP NOT NULL

ALTER TABLE employees ALTER COLUMN department SET NOT NULL;

If every existing row already has a real value in department, this succeeds immediately. But if even one row currently has NULL there, it fails:

ALTER TABLE employees ALTER COLUMN department SET NOT NULL;

ERROR: column "department" of relation "employees" contains null values

The fix is to clean up the existing data first, then apply the constraint:

UPDATE employees SET department = 'Unassigned' WHERE department IS NULL;
ALTER TABLE employees ALTER COLUMN department SET NOT NULL;

Removing the constraint doesn’t have this problem, there’s no data to conflict with:

ALTER TABLE employees ALTER COLUMN department DROP NOT NULL;

Try It

1. Add a DEFAULT 'General' to a team column, insert a row without mentioning team, and confirm it picked up the default.
2. Remove that default, insert another row without mentioning team, and confirm it’s NULL this time.
3. Try SET NOT NULL on a column that currently has at least one NULL value, and confirm you get an error, then clean up the NULL and try again successfully.
4. Explain, in your own words, why SET NOT NULL needs to check existing data, while SET DEFAULT doesn’t.

Recap

• SET DEFAULT and DROP DEFAULT change what future INSERTs fill in automatically, without touching existing rows.
• SET NOT NULL requires every existing row to already have a real value, or it fails, existing NULLs need cleaning up first.
• DROP NOT NULL always succeeds, removing a constraint never conflicts with existing data.

Next lesson: adding and dropping UNIQUE and CHECK constraints on a table that already exists.