SQL CONSTRAINTS

What are SQL CONSTRAINTS?

Rules attached to a table or column that the database automatically enforces to preserve data integrity.

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 CONSTRAINTS:

SQL CONSTRAINTS Full Explanation

SQL CONSTRAINTS are declarative rules that the database engine evaluates whenever data is inserted, updated, or deleted. They guarantee that each row and relationship in a table stays valid. Constraints can be defined inline at the column level or separately at the table level. The most common types are:- PRIMARY KEY – identifies each row uniquely and implies NOT NULL and UNIQUE.- FOREIGN KEY – links a column or group of columns to the PRIMARY KEY or UNIQUE key of another table, enforcing referential integrity.- UNIQUE – prevents duplicate values within the constrained column set.- CHECK – validates that each value satisfies a Boolean expression.- NOT NULL – disallows NULL values in a column.- DEFAULT – supplies a value when none is provided.Constraints are validated on every write operation. Some dialects allow deferring validation until COMMIT, temporarily disabling a constraint, or creating it as NOT VALID to validate later. Naming constraints (CONSTRAINT my_name ...) simplifies maintenance and error handling. Poorly designed or missing constraints can lead to orphaned rows, duplicate records, and hard-to-trace bugs.Because constraints execute automatically inside the database engine, they are faster, safer, and easier to audit than manual checks in application code.

SQL CONSTRAINTS Syntax

-- Column-level
CREATE TABLE t (
    id INT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    price NUMERIC CHECK (price > 0)
);

-- Table-level
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Add later
ALTER TABLE orders ADD CONSTRAINT chk_positive_qty CHECK (quantity > 0);

SQL CONSTRAINTS Parameters

Example Queries Using SQL CONSTRAINTS

-- 1. Create a table with multiple constraints
CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC CHECK (price > 0),
  category_id INT,
  CONSTRAINT fk_category FOREIGN KEY (category_id)
    REFERENCES categories(id)
);

-- 2. Insert valid row
INSERT INTO products (sku, name, price, category_id)
VALUES ('A100', 'Widget', 25.00, 1);

-- 3. Attempt to insert a duplicate primary key
INSERT INTO products (sku, name, price)
VALUES ('A100', 'Gadget', 30.00);

-- 4. Attempt to insert a negative price
INSERT INTO products (sku, name, price)
VALUES ('B200', 'Bad Widget', -5.00);

Expected Output Using SQL CONSTRAINTS

Table created successfully.
One row inserted.
Error: duplicate key value violates PRIMARY KEY constraint.
Error: new row violates CHECK constraint (price > 0).

Use Cases with SQL CONSTRAINTS

Guarantee unique identifiers with PRIMARY KEY and UNIQUE.
Enforce valid relationships between parent and child tables with FOREIGN KEY.
Prevent invalid values such as negative quantities or out-of-range dates using CHECK.
Disallow missing mandatory data with NOT NULL.
Provide default timestamps, statuses, or counters with DEFAULT.
Simplify application logic by delegating validation to the database.

Common Mistakes with SQL CONSTRAINTS

Assuming UNIQUE implies NOT NULL (it does not).
Forgetting to index FOREIGN KEY columns, leading to slow joins and deletes.
Confusing column-level and table-level syntax.
Omitting constraint names, making later ALTER or DROP operations harder.
Expecting DEFAULT to override explicit NULL values (most dialects insert NULL unless NOT NULL is also present).

First Introduced In

SQL-92

Frequently Asked Questions

How do I add a constraint after the table exists?

Use ALTER TABLE ADD CONSTRAINT. Example:```ALTER TABLE employeesADD CONSTRAINT uq_email UNIQUE (email);```

What happens if a row violates a constraint?

The database aborts the statement, rolls back that statement’s changes, and returns an error describing the violated constraint.

Are constraint names required?

No, but naming them (CONSTRAINT constraint_name ...) is best practice because it eases troubleshooting and future ALTER or DROP operations.

Can a table have multiple CHECK constraints?

Yes. You can declare any number of CHECK clauses, each evaluating its own Boolean condition. Every condition must be true for a row to be accepted.