SQL Constraints

What Are Constraints?

In SQL, constraints are rules applied to table columns to enforce data integrity and prevent invalid or inconsistent data from being inserted. They help ensure that the data in the database remains accurate, meaningful, and reliable.

Common types of constraints include:

PRIMARY KEY: Ensures each row has a unique and non-null identifier.
UNIQUE: Ensures all values in a column are distinct.
CHECK: Enforces a specific condition on column values.
NOT NULL: Prevents null (empty) values in a column.
FOREIGN KEY: Ensures that a value in one table matches a value in another table, maintaining referential integrity.

CREATE TABLE users (
  id INT PRIMARY KEY,                -- Unique, non-null identifier
  email VARCHAR(100) UNIQUE,         -- Email must be unique across all users
  age INT CHECK (age >= 18)          -- Age must be at least 18
);

This example defines three constraints:

PRIMARY KEY on id: guarantees each user has a unique ID.
UNIQUE on email: prevents duplicate email addresses.
CHECK on age: ensures only users 18 or older can be added.

Constraints are essential for building robust, secure, and logically consistent databases. They also reduce the need for manual data validation in applications.

NOT NULL Constraint

The name column must always have a value.

CREATE TABLE employees (
  id INT,
  name VARCHAR(100) NOT NULL
);

UNIQUE Constraint

Ensures no two usernames are the same.

CREATE TABLE accounts (
  username VARCHAR(50) UNIQUE
);

PRIMARY KEY Constraint

The primary key uniquely identifies each row and cannot be null.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100)
);

FOREIGN KEY Constraint

Links orders to customers.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CHECK Constraint

Ensures that price is greater than zero.

CREATE TABLE products (
  name VARCHAR(100),
  price DECIMAL(10,2) CHECK (price > 0)
);

DEFAULT Constraint

If no theme is specified, 'light' is used.

CREATE TABLE settings (
  theme VARCHAR(20) DEFAULT 'light'
);

Adding Constraints to Existing Tables

Use ALTER TABLE to add constraints after creation.

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

Best Practices

Use NOT NULL and CHECK to validate data input
Always define PRIMARY KEY and FOREIGN KEY for relational integrity
Prefer DEFAULT values for consistent fallbacks

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic

Menu