How to Add Constraint to Existing Table in PostgreSQL

To add a constraint to an existing table in PostgreSQL, use the ALTER TABLE statement followed by ADD CONSTRAINT and the constraint definition. For example, ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition); adds a check constraint to the table.

Syntax

The basic syntax to add a constraint to an existing table is:

• ALTER TABLE table_name: Specifies the table to modify.
• ADD CONSTRAINT constraint_name: Names the new constraint.
• constraint_type (columns or condition): Defines the type and details of the constraint, such as PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY.

sql

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

-- Example for CHECK constraint
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

Example

This example shows how to add a CHECK constraint to an existing table employees to ensure the age column is at least 18.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

ALTER TABLE employees
ADD CONSTRAINT age_check CHECK (age >= 18);

-- Insert valid data
INSERT INTO employees (name, age) VALUES ('Alice', 30);

-- Insert invalid data (will fail)
INSERT INTO employees (name, age) VALUES ('Bob', 16);

Output

INSERT 0 1
ERROR:  new row for relation "employees" violates check constraint "age_check"
DETAIL:  Failing row contains (2, Bob, 16).

Common Pitfalls

Common mistakes when adding constraints include:

• Not naming the constraint, which can make future management harder.
• Adding constraints that existing data violates, causing errors.
• Using incorrect syntax or forgetting parentheses around conditions.

Always check existing data before adding constraints to avoid failures.

sql

/* Wrong: Missing parentheses in CHECK condition */
ALTER TABLE employees
ADD CONSTRAINT age_check CHECK age >= 18;

/* Correct: Parentheses included */
ALTER TABLE employees
ADD CONSTRAINT age_check CHECK (age >= 18);

Quick Reference

Constraint Type	Syntax Example	Purpose
PRIMARY KEY	ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column);	Uniquely identifies each row
UNIQUE	ALTER TABLE table_name ADD CONSTRAINT unique_name UNIQUE (column);	Ensures all values in a column are unique
CHECK	ALTER TABLE table_name ADD CONSTRAINT check_name CHECK (condition);	Validates data meets a condition
FOREIGN KEY	ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES other_table(column);	Enforces referential integrity

Key Takeaways

Use ALTER TABLE ADD CONSTRAINT to add constraints to existing tables in PostgreSQL.

Always name your constraints for easier management and error identification.

Check existing data before adding constraints to avoid errors.

Use parentheses around conditions in CHECK constraints.

Common constraints include PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY.