Menu
MySQL

Constraints

In SQL, constraints are rules applied to table columns that ensure the integrity, validity, and accuracy of the data stored within the database. Constraints define the data that can enter a table and help maintain data consistency. SQL supports various types of constraints to enforce these rules, and they can be applied at the column level or the table level.

 

 

Types of SQL Constraints

Here are the most commonly used constraints in SQL:

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK
  6. DEFAULT

 

 

1. NOT NULL Constraint

  • Purpose: Ensures that a column cannot store NULL values.
  • Usage: Used when a field must always contain a value and cannot be left empty.

Example:

CREATE TABLE Employees (
   EmployeeID INT NOT NULL,
   FirstName VARCHAR(50) NOT NULL,
   LastName VARCHAR(50) NOT NULL,
   HireDate DATE
);
 

In this example, the columns EmployeeID, FirstName, and LastName cannot contain NULL values. They must always have a value when a new record is inserted.

 

 

 

2. UNIQUE Constraint

  • Purpose: Ensures that all values in a column (or a set of columns) are unique across the table, meaning no two rows can have the same value in that column.
  • Usage: Used when a field should contain unique values (e.g., email addresses or social security numbers).

Example:

CREATE TABLE Employees (
   EmployeeID INT NOT NULL,
   Email VARCHAR(100) UNIQUE,
   FirstName VARCHAR(50),
   LastName VARCHAR(50)
);
 

In this example, the Email column must contain unique values for every row. No two employees can have the same email address.

 

 

 

3. PRIMARY KEY Constraint

  • Purpose: A combination of the UNIQUE and NOT NULL constraints. It uniquely identifies each record in a table. Each table can only have one PRIMARY KEY, but it can span multiple columns (composite key).
  • Usage: Used to enforce a unique identifier for each record in a table.

Example:

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50)
);
 

Here, EmployeeID is the PRIMARY KEY. This means no two employees can have the same EmployeeID, and it cannot be NULL.

 

Composite Primary Key Example:

CREATE TABLE Orders (
   OrderID INT,
   ProductID INT,
   PRIMARY KEY (OrderID, ProductID)
);

 

In this example, the PRIMARY KEY is made up of two columns, OrderID and ProductID, meaning that the combination of both must be unique.

 

 

 

4. FOREIGN KEY Constraint

  • Purpose: Enforces a link between two tables. The FOREIGN KEY in one table points to the PRIMARY KEY in another table. This ensures that the relationship between tables is maintained and prevents invalid data from being entered.
  • Usage: Used to maintain referential integrity between two related tables.

Example:

CREATE TABLE Departments (
   DepartmentID INT PRIMARY KEY,
   DepartmentName VARCHAR(100)
);
CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
 

In this example, DepartmentID in the Employees table is a FOREIGN KEY referencing the DepartmentID in the Departments table. This means that an employee can only be assigned to a department that already exists in the Departments table.

 

 

 

5. CHECK Constraint

  • Purpose: Ensures that a condition is met before data can be inserted or updated in a column. If the condition is not satisfied, the data modification is rejected.
  • Usage: Used when you need to enforce a custom rule on a column’s values.

Example:

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Age INT CHECK (Age >= 18)
);
 

In this example, the CHECK constraint ensures that the value of Age must be 18 or greater. Any attempt to insert a record with an Age less than 18 will fail.

 

 

 

6. DEFAULT Constraint

  • Purpose: Provides a default value for a column when no value is specified during data insertion.
  • Usage: Used to automatically assign a default value to a column when no other value is provided.

Example:

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   HireDate DATE DEFAULT CURRENT_DATE
);
 

In this example, if no hire date is provided when inserting a new employee, the HireDate will default to the current date.

 

 

MySQL