SQL guide · Constraints

SQL ADD CONSTRAINT: Add Rules to an Existing Table

ADD CONSTRAINT is the part of an ALTER TABLE statement that attaches a new rule to a table that already exists. You use it when a table was created without a key or validation rule, when requirements change, or when you load data first and enforce rules afterwards. It can add primary keys, foreign keys, unique constraints and check constraints, and in some databases default constraints.

The database validates the existing data when you add the constraint. If any row violates the rule, the statement fails and nothing changes. That is why the normal workflow is to query for problem rows first, repair them, then add the constraint. For example, before adding a unique constraint on email you would group by email and look for counts above one, and before a foreign key you would left join to the parent table looking for missing matches.

Always name your constraints with CONSTRAINT name. A name appears in error messages and is needed to drop the constraint later. Without one, the database invents a name, which differs from environment to environment and makes migration scripts fragile. On large production tables, adding a constraint can take locks while the engine scans the data, so check how your database handles it and run changes in a quiet period when needed.

Basic syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, ...);

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, ...);

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column1)
  REFERENCES other_table (column1);

Replace the constraint type and its definition as needed. Each ADD CONSTRAINT clause adds exactly one named rule, although some databases let you list several clauses in a single ALTER TABLE separated by commas.

SQL ADD CONSTRAINT examples

Add a primary key

A table loaded without a key can be given one afterwards. The columns must contain unique, non-null values, so check for duplicates first. In SQL Server the column must already be NOT NULL.

ALTER TABLE products
ADD CONSTRAINT pk_products PRIMARY KEY (product_id);

Add a foreign key

This links orders.customer_id to customers. The first query finds orders whose customer does not exist, which would otherwise cause the statement to fail.

SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id)
  ON DELETE RESTRICT;

Add unique and check constraints together

Several rules can be added to the same table in separate statements. After these run, duplicate emails and negative salaries are both rejected.

ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);

ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);

Add several constraints in one statement

PostgreSQL, MySQL and Oracle accept more than one clause in a single ALTER TABLE (Oracle needs them as separate statements in older releases). Doing this in one pass can avoid scanning the table twice.

-- PostgreSQL
ALTER TABLE orders
  ADD CONSTRAINT pk_orders PRIMARY KEY (order_id),
  ADD CONSTRAINT chk_total_nonneg CHECK (total >= 0);

Where SQL dialects differ

PostgreSQL: Supports all constraint types through ADD CONSTRAINT. Adding NOT VALID to a foreign key or check skips scanning old rows and takes a lighter lock; run VALIDATE CONSTRAINT afterwards. You can add a unique or primary key USING INDEX an existing unique index to avoid a table rewrite.

MySQL: Supports primary key, unique, foreign key (InnoDB only) and, from 8.0.16, check constraints. A foreign key will create a supporting index automatically if needed. Many ALTER TABLE operations rebuild the table, so use online DDL options on large tables.

SQL Server: Supports all five types, including ADD CONSTRAINT name DEFAULT value FOR column. WITH NOCHECK skips validation of existing rows but leaves the constraint untrusted, so use it sparingly. Primary keys require NOT NULL columns.

SQLite: ALTER TABLE ADD CONSTRAINT is not supported. To add a constraint, create a new table with the rule, copy the rows across, drop the old table and rename the new one. A UNIQUE rule can be added with CREATE UNIQUE INDEX instead.

Oracle: Supports primary, unique, foreign and check constraints with ADD CONSTRAINT. Use ENABLE NOVALIDATE to enforce a rule for new rows only, or DISABLE to define it without enforcement during data loads. Defaults and NOT NULL use MODIFY instead.

Common mistakes

  • Adding a constraint without checking existing data. One violating row makes the whole statement fail, sometimes after a long scan.
  • Omitting the constraint name, then discovering that each environment generated a different system name that your drop script cannot find.
  • Running a blocking ALTER TABLE on a busy production table without understanding the lock. Use NOT VALID, online DDL or off-hours windows.
  • Using this syntax in SQLite and expecting it to work. Rebuild the table instead.
  • Trying to add NOT NULL or DEFAULT with the same form as the others. Those usually need ALTER COLUMN or MODIFY.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator