SQL Constraints

SQL Tutorials

SQL constrains are rules to use the data inserted into a table. They help keep data clean, correct, and meaningful. When you use constraints, the database stops wrong or duplicate data before it gets stored.

These constraints are guards that protect your data.

Why SQL Constraints Are Important

SQL constraints help you
Avoid blank entries
Avoid duplication of data
Keep relationships between tables correct
Minimize errors in applications

Because of this good database design always involve constraints.

Types of SQL Constraints

1. NOT NULL Constraint

The NOT NULL constraint enforces a column to not to have null or empty values.

Example

CREATE TABLE users (
  user_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

Here
Values for user_id and name are required.
Email could be blank.

2. UNIQUE Constraint

The UNIQUE constraint applies to ensure all the values in a column are unique.

Example

CREATE TABLE users (
  email VARCHAR(100) UNIQUE
);

As this would prevent two users from using the same email.

3. PRIMARY KEY Constraint

The PRIMARY KEY uniquely identifies each row in a table. Every table should have one primary key.

Rules of PRIMARY KEY

A primary key
must be unique
cannot be NULL
exists only once per table

Example

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name VARCHAR(100)
);

Each user gets a unique user_id. The database will not accept any duplicate or NULL values.

4. FOREIGN KEY Constraint

FOREIGN KEY links two tables together. The foreign key ensures that the data in the table corresponds to the data in the other tables.

Example

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Here, User_id in orders must be exist in users table
orders cannot link to invalid users

5. CHECK Constraint

The CHECK constraint permits only those values that satisfy a predicate.

Example

CREATE TABLE products (
  price INT CHECK (price > 10)
);

This makes the price always greater than ten.

6. DEFAULT Constraint

The DEFAULT constraint is introduced if it is desired to set a default value when no value is supplied.

Example

CREATE TABLE users (
  status VARCHAR(20) DEFAULT 'active'
);

In case, if there is no status is assigned it will be active bydefault in the database.

Adding Constraints to an Existing Table

You can also add constraints after creation of table.

Example

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

Removing Constraints

The constraint can be removed if desirable.

Example

ALTER TABLE users
DROP CONSTRAINT unique_email;

Simple Practice Example

Create Table

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age >= 18),
  status VARCHAR(20) DEFAULT 'active'
);

Valid Insert

INSERT INTO students (student_id, name, email, age)
VALUES (1, 'Amit', 'amit@example.com', 20);

Invalid Insert

INSERT INTO students (student_id, name, age)
VALUES (1, NULL, 16);

This will not work because
student_id is duplicate
name is NULL
age is less than 18

Summary

SQL constraints are used to limit the data stored in tables.
They avoid wrong or duplicate data.
The PRIMARY KEY and FOREIGN KEY are used for defining the relationship between tables.
The use of constraints guarantees that databases are reliable and safe.