Why constraints matter in SQL

Introduction

Constraints help keep data correct and organized in a database. They stop mistakes and make sure information follows rules.

When you want to make sure every user has a unique ID.

When you need to prevent entering a negative number for age.

When you want to ensure a product price is always filled in.

When you want to link orders to existing customers only.

When you want to avoid duplicate email addresses in a list.

Syntax

SQL

CREATE TABLE table_name (
  column_name datatype CONSTRAINT constraint_name constraint_type,
  ...
);

Constraints can be added when creating a table or later with ALTER TABLE.

Common constraint types include PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY.

Examples

This creates a Users table where UserID must be unique and not empty, Email must be unique, and Age cannot be negative.

SQL

CREATE TABLE Users (
  UserID INT PRIMARY KEY,
  Email VARCHAR(100) UNIQUE,
  Age INT CHECK (Age >= 0)
);

This adds a rule to Orders so CustomerID must match an existing CustomerID in Customers table.

SQL

ALTER TABLE Orders
ADD CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Sample Program

This creates a Products table with rules: ProductID must be unique, ProductName cannot be empty, and Price must be more than zero. The second insert will fail because Price is negative.

SQL

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(50) NOT NULL,
  Price DECIMAL(10,2) CHECK (Price > 0)
);

INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Pen', 1.50);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (2, 'Notebook', -5.00);

Important Notes

Constraints help catch errors early, saving time and confusion later.

Without constraints, bad data can cause wrong reports or broken apps.

Some databases show clear error messages when constraints are broken.

Summary

Constraints keep data accurate and reliable.

They enforce rules like uniqueness, required values, and valid ranges.

Using constraints helps prevent mistakes and keeps your database healthy.