CodingNic
Sign In Book Session

Altering Tables

Adding and Dropping UNIQUE and CHECK

Altering Tables 10 min read

Adding and Dropping UNIQUE and CHECK

Objectives

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

  • Add a UNIQUE constraint to an existing column
  • Add a CHECK constraint to an existing column
  • Remove either one with DROP CONSTRAINT

💡 Why this matters: UNIQUE and CHECK (Module 3) don’t have to be decided at CREATE TABLE time either. A rule discovered later, “emails must be unique,” “salary must be positive,” can be added to a table that’s already in use.

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

sql
ALTER TABLE employees ADD CONSTRAINT employees_email_key UNIQUE (email);

This is a slightly different shape than the constraints seen so far, ADD CONSTRAINT name UNIQUE (column) names the constraint explicitly (constraint naming gets its own lesson next), rather than being declared inline inside CREATE TABLE. Once added, it behaves exactly like a UNIQUE declared from the start, a duplicate email is rejected the same way covered in Module 3.

Adding CHECK

sql
ALTER TABLE employees ADD CONSTRAINT employees_salary_check CHECK (salary > 0);
text
INSERT INTO employees (first_name, last_name, email, salary, department)
VALUES ('Jordan', 'Blake', 'jordan.blake@example.com', -100, 'Sales');

ERROR: new row for relation "employees" violates check constraint "employees_salary_check"

Exactly like a CHECK declared inline (Module 3), this enforces the condition on every future INSERT and UPDATE. Adding a CHECK to a table that already has data also validates every existing row against it, adding a constraint that existing data already violates fails immediately, the same way SET NOT NULL does in the last lesson.

Dropping Either One

Both are removed the same way, by name:

sql
ALTER TABLE employees DROP CONSTRAINT employees_email_key;
ALTER TABLE employees DROP CONSTRAINT employees_salary_check;

This is exactly why naming a constraint clearly matters, DROP CONSTRAINT needs to know precisely which one to remove.

Try It

  1. Add a UNIQUE constraint named employees_email_key to the email column, then try inserting a duplicate email and confirm it’s rejected.
  2. Add a CHECK constraint named employees_salary_check requiring salary > 0, then try inserting a negative salary and confirm it’s rejected.
  3. Drop both constraints by name, and confirm the same inserts that failed before now succeed.
  4. Explain, in your own words, why adding a CHECK constraint to a table that already has data might fail immediately, before any new row is even inserted.

Recap

  • ALTER TABLE ... ADD CONSTRAINT name UNIQUE (column) and ADD CONSTRAINT name CHECK (condition) add these constraints to an existing table.
  • Adding either one validates existing data first, a table with data that already violates the new rule can’t have it added until the data is fixed.
  • ALTER TABLE ... DROP CONSTRAINT name removes a constraint by its name.

Next lesson: adding and dropping PRIMARY KEY and FOREIGN KEY, the constraints that define a table’s identity and its relationships.