SQL Integrity Constraints

Estimated study time: 14 minutes. Rules that keep your data accurate and reliable.

Integrity constraints are rules enforced at the database level to make sure the data stored in a table stays accurate, consistent, and reliable. Instead of relying on application code to police every insert or update, SQL lets you push these rules down into the schema itself.

Why Integrity Constraints Matter

Without constraints, nothing stops duplicate primary keys, orphaned foreign keys, or missing required fields from creeping into a table. Constraints act as a safety net that the database engine checks automatically on every DML operation.

Types of Integrity Constraints

1. NOT NULL Constraint

Ensures a column can never store a NULL value.

CREATE TABLE Employees (
  EmpID INT NOT NULL,
  EmpName VARCHAR(100) NOT NULL
);

2. UNIQUE Constraint

Guarantees all values in a column (or set of columns) are distinct.

CREATE TABLE Employees (
  Email VARCHAR(255) UNIQUE
);

3. PRIMARY KEY Constraint

Combines NOT NULL and UNIQUE to uniquely identify each row in a table.

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  EmpName VARCHAR(100)
);

4. FOREIGN KEY Constraint

Maintains referential integrity by linking a column to the primary key of another table.

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  EmpID INT,
  FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)
);

5. CHECK Constraint

Restricts the values allowed in a column based on a logical condition.

CREATE TABLE Employees (
  Age INT CHECK (Age >= 18)
);

6. DEFAULT Constraint

Assigns a fallback value when no value is supplied during an insert.

CREATE TABLE Employees (
  JoiningDate DATE DEFAULT GETDATE()
);

Dropping a Constraint

ALTER TABLE Employees
DROP CONSTRAINT CK_Employees_Age;

Used together, these constraints form the backbone of a well-designed schema, catching bad data before it ever gets a chance to cause problems downstream.