SQL ADD CONSTRAINT

What is SQL ADD CONSTRAINT?

Adds a named rule (primary key, foreign key, unique, check, or default) to an existing table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ADD CONSTRAINT: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite (limited), Snowflake, Redshift

SQL ADD CONSTRAINT Full Explanation

ADD CONSTRAINT is used with ALTER TABLE to attach a new integrity rule to a table after it has been created. By naming the constraint you can later reference it to modify or drop it. Supported constraint types include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT. The database immediately validates the rule against existing data unless you specify options such as NOT VALID (PostgreSQL) or WITH NOCHECK (SQL Server). If existing rows violate the rule, the statement fails unless the dialect supports and you choose deferred validation. Adding constraints can implicitly create supporting indexes (for example, most systems build an index for a new primary key or unique constraint). Be mindful of lock levels: some engines require a table rewrite or blocking lock while the constraint is validated.

SQL ADD CONSTRAINT Syntax

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name constraint_type
  [constraint_body];

SQL ADD CONSTRAINT Parameters

table_name (identifier) - Name of the table being altered
constraint_name (identifier) - User-defined name for the constraint
constraint_type (keyword) - PRIMARY KEY|||FOREIGN KEY|||UNIQUE|||CHECK|||DEFAULT
constraint_body (varies) - Column list or expression defining the rule

Example Queries Using SQL ADD CONSTRAINT

-- Add a primary key
ALTER TABLE customers
  ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

-- Add a foreign key with cascade delete
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE CASCADE;

-- Add a check constraint
ALTER TABLE products
  ADD CONSTRAINT price_positive_chk CHECK (price > 0);

-- Add a unique constraint across two columns
ALTER TABLE users
  ADD CONSTRAINT email_env_unique UNIQUE (email, environment);

-- Add a default value (SQL Server syntax)
ALTER TABLE invoices
  ADD CONSTRAINT invoices_created_default DEFAULT (GETDATE()) FOR created_at;

Expected Output Using SQL ADD CONSTRAINT

If all existing rows satisfy the rule, the table is altered and the constraint is stored in the system catalog
Future inserts or updates that violate the rule are rejected
Some engines also create an index to enforce primary key or unique constraints

Use Cases with SQL ADD CONSTRAINT

Enforcing entity integrity with primary keys
Maintaining referential integrity between parent and child tables
Guaranteeing business rules such as positive quantities or valid date ranges
Preventing duplicate rows in combination columns
Setting standardized default values while keeping the rule easy to drop or change

Common Mistakes with SQL ADD CONSTRAINT

Forgetting to supply a constraint name and relying on auto-generated names, making future maintenance harder
Creating a foreign key that references a non-indexed primary key, causing performance issues
Adding a CHECK that evaluates to UNKNOWN for NULL values when NULLs should be disallowed
Attempting to add the constraint while data already violates it, causing the statement to fail
Omitting required parentheses around column lists or expressions

First Introduced In

SQL-92 standard

Frequently Asked Questions

What types of constraints can I add with ADD CONSTRAINT?

You can add PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT constraints. Support for DEFAULT with ADD CONSTRAINT varies by dialect (native in SQL Server).

How do I drop a constraint I added?

Use ALTER TABLE table_name DROP CONSTRAINT constraint_name; Always record the name when you create the constraint so dropping it is straightforward.

Will adding a constraint impact performance?

Validating the constraint can be costly on large tables, and supporting indexes increase storage. However, query performance often improves for joins and lookups on the constrained columns.

Can I defer constraint checks until commit?

Some databases (PostgreSQL, Oracle) allow DEFERRABLE constraints that are checked at COMMIT. Specify DEFERRABLE INITIALLY DEFERRED when creating or altering the constraint.