# SQL ADD CONSTRAINT: Add Rules to an Existing Table - URL: https://betteranalyst.com/sql/add-constraint SQL guide ยท Constraints `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 - [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table) - [SQL DROP CONSTRAINT](https://betteranalyst.com/sql/drop-constraint) - [SQL PRIMARY KEY](https://betteranalyst.com/sql/primary-key) - [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key) - [SQL UNIQUE](https://betteranalyst.com/sql/unique) - [SQL CHECK](https://betteranalyst.com/sql/check) [Browse all SQL guides](https://betteranalyst.com/sql) ## 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](https://betteranalyst.com/ai-sql-query-generator)