SQL CONSTRAINT

What is an SQL CONSTRAINT?

CONSTRAINT defines rules that the data in a table must follow, guaranteeing integrity and consistency.

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 CONSTRAINT: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery (partial)

SQL CONSTRAINT Full Explanation

CONSTRAINT is an umbrella term for integrity rules such as PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and NOT NULL. You can declare a constraint inline on a single column or as a table-level clause that references one or more columns. When a constraint is violated, the database rejects the statement that caused the violation, thus protecting data quality. Constraints can be named explicitly, which simplifies debugging and later alteration. They can also be added or dropped after table creation with ALTER TABLE. Some databases support DEFERRABLE and INITIALLY DEFERRED options, letting you delay enforcement until commit. Understanding constraint behavior is essential when loading bulk data, designing relationships, or tuning performance, because constraint checks add overhead but prevent costly data errors.

SQL CONSTRAINT Syntax

-- Column-level constraint
CREATE TABLE table_name (
    column_name data_type CONSTRAINT constraint_name constraint_type,
    ...
);

-- Table-level constraint
CREATE TABLE table_name (
    column_name1 data_type,
    column_name2 data_type,
    CONSTRAINT constraint_name constraint_type (column_name1, column_name2),
    ...
);

-- Add a constraint later
ALTER TABLE table_name
    ADD CONSTRAINT constraint_name constraint_type (column_list);

-- Drop a constraint
ALTER TABLE table_name
    DROP CONSTRAINT constraint_name;

SQL CONSTRAINT Parameters

constraint_type ENUM (PRIMARY KEY) - UNIQUE|||FOREIGN KEY|||CHECK|||NOT NULL.
column_list (LIST) - One or more column names affected by the rule.
reference_table STRING For FOREIGN KEY - target table.
reference_cols (LIST) - For FOREIGN KEY|||target columns.
expression (STRING For CHECK) - Boolean condition.
DEFERRABLE (FLAG) - Postgres, Oracle|||allows deferred checking.
ON DELETE/UPDATE ACTION ENUM CASCADE (SET NULL) - RESTRICT, etc.

Example Queries Using SQL CONSTRAINT

-- 1. Primary key and unique constraint
CREATE TABLE customers (
    id SERIAL CONSTRAINT customers_pk PRIMARY KEY,
    email TEXT CONSTRAINT customers_email_uk UNIQUE
);

-- 2. Foreign key with cascade
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
        REFERENCES customers(id) ON DELETE CASCADE
);

-- 3. Add a check constraint later
ALTER TABLE customers
    ADD CONSTRAINT customers_email_chk CHECK (email LIKE '%@%');

Expected Output Using SQL CONSTRAINT

Each statement succeeds only if the new or existing data satisfies the rule
Violations raise an error (e
g
, unique_violation, check_violation) and the transaction is rolled back unless handled

Use Cases with SQL CONSTRAINT

Enforce entity uniqueness with PRIMARY KEY or UNIQUE
Maintain referential integrity between tables with FOREIGN KEY
Restrict allowable values or ranges using CHECK
Prevent nulls in mandatory fields with NOT NULL
Safely bulk-load data by temporarily deferring constraint checks

Common Mistakes with SQL CONSTRAINT

Omitting explicit names, making later maintenance harder
Adding a FOREIGN KEY when child rows already violate the rule
Assuming NOT NULL is implied by PRIMARY KEY in all dialects (it is not in some older systems)
Forgetting to index foreign key columns, leading to slow deletes or updates
Misusing CHECK for complex subqueries unsupported by some engines

First Introduced In

ANSI SQL-92

Frequently Asked Questions

What types of constraints exist?

PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and NOT NULL are the most common. Each targets a different aspect of data integrity.

Can I rename a constraint?

Use ALTER TABLE ... RENAME CONSTRAINT in PostgreSQL or sp_rename in SQL Server. Some systems require dropping and recreating the rule.

Why did my FOREIGN KEY add fail?

Existing rows in the child table probably reference non-existent parent keys. Clean or update the data before adding the constraint.

Are constraints automatically indexed?

PRIMARY KEY and UNIQUE create indexes in all major databases. FOREIGN KEY columns are not always indexed automatically; create one manually for better performance.