How To Use ADD CONSTRAINT in SQL

SQL constraints can be defined at table creation or added later to an existing table using ADD CONSTRAINT SQL syntax. The latter is common in real-world development, where schemas evolve and requirements change after data is already in place. 

This article covers the syntax, constraint types, production considerations, and how constraint enforcement works in a distributed SQL environment like YugabyteDB.

What Is ADD CONSTRAINT in SQL?

ADD CONSTRAINT is a clause used with ALTER TABLE to apply a named constraint to an existing table. The general syntax looks like this:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

Naming the constraint is optional in some dialects, but it’s considered best practice. A named constraint is easy to reference when you need to drop or modify it later. Without a name, the database assigns one automatically, and those generated names are rarely easy to work with in production.

What Constraint Types Can You Add With ADD CONSTRAINT?

YSQL supports the standard PostgreSQL constraint types. Each one can be applied to an existing table using ALTER TABLE … ADD CONSTRAINT. Here’s how each looks in practice:

PRIMARY KEY

ALTER TABLE orders
ADD CONSTRAINT orders_pkey PRIMARY KEY (order_id);

In YugabyteDB, adding a PRIMARY KEY to an existing table triggers a full table rewrite. Because YugabyteDB distributes data across tablets based on the primary key, all existing rows must be rewritten and redistributed to reflect the new key structure. 

This is not a lightweight operation on a large table. Wherever possible, define primary keys at table creation. See the YSQL primary keys docs for guidance.

FOREIGN KEY

ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

This enforces referential integrity between two tables. YugabyteDB validates all existing rows against the referenced table when you run this statement.

UNIQUE

ALTER TABLE users
ADD CONSTRAINT users_email_unq UNIQUE (email);

Adds a uniqueness requirement on the specified column. YugabyteDB backs this with a unique index, which is created as part of the operation.

CHECK

ALTER TABLE products
ADD CONSTRAINT products_price_positive CHECK (price > 0);

Validates that rows satisfy a boolean expression. All existing rows are checked when the constraint is added.

NOT NULL

NOT NULL is a special case. In YSQL (following PostgreSQL behavior), it’s applied through a different syntax:

ALTER TABLE products
ALTER COLUMN price SET NOT NULL;

You cannot add NOT NULL via ADD CONSTRAINT the way you would with PRIMARY KEY or UNIQUE. This is a common point of confusion for developers coming from other SQL dialects.

How Do You Name and Drop a Constraint?

Named constraints are straightforward to remove. Use DROP CONSTRAINT with the name you assigned:

ALTER TABLE orders
DROP CONSTRAINT orders_customer_fk;

This is why naming matters. If the constraint was created without a name, you’d need to look up the system-generated name in information_schema.table_constraints or pg_constraint before you could drop it. The extra step is avoidable.

What Should You Know Before Adding a Constraint to a Production Table?

Two things matter most when adding a constraint to a live table.

Existing data must satisfy the constraint. When you run ADD CONSTRAINT, the database validates all existing rows immediately. If any row violates the constraint, the entire operation fails, and the constraint is not applied. Run a validation query against your data before issuing the ALTER TABLE statement. For example, before adding a CHECK (price > 0) constraint, verify that no rows have price <= 0.

Adding a PRIMARY KEY triggers a table rewrite in YugabyteDB. Because YugabyteDB distributes rows across tablets based on the primary key, adding one post-creation requires a full redistribution of all table data. On a large production table, this is an expensive, time-consuming operation. Plan accordingly and schedule it during a maintenance window. The YSQL ALTER TABLE docs cover the full set of supported operations and any current limitations.

How Does ADD CONSTRAINT Work in a Distributed SQL Database?

In a single-node database, constraint enforcement is local. In a distributed SQL database, it needs to be consistent across every node in the cluster. That’s where YugabyteDB’s architecture makes a meaningful difference.

Constraint enforcement is distributed. In YugabyteDB, constraints like UNIQUE and FOREIGN KEY are enforced at the distributed storage layer, not just on a coordinator node. A uniqueness check is backed by a globally distributed index rather than a per-shard check, so it catches violations no matter which node receives the write, without needing to scan the whole cluster to do it.

Enforcement is ACID-compliant. YugabyteDB enforces constraints within its full ACID transaction model, including across distributed tablets. A constraint violation rolls back the entire transaction cleanly, consistent with what you’d expect from a PostgreSQL-compatible database. There are no partial writes and no silent failures.

For more on the distributed SQL architecture that makes this possible, see the distributed SQL key concept.

ADD CONSTRAINT in YSQL follows standard PostgreSQL syntax, with the added guarantee that enforcement holds consistently across a distributed cluster.

If you’re working with a schema that needs constraint changes on a live, distributed table, try YugabyteDB Aeon for free, or book a demo to see how it handles your workload.