SQL Constraints – Basics

SQL Constraints Overview

Constraints enforce rules at the table level to ensure data integrity. They are defined when creating or altering tables and can be applied to one or more columns.

Primary Key (click to expand)

A primary key uniquely identifies each row in a table. Only one primary key is allowed per table.

CREATE TABLE Employees ( EmployeeID INT NOT NULL, FirstName NVARCHAR(50), LastName NVARCHAR(50), CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID) );

Foreign Key (click to expand)

Enforces a link between data in two tables, ensuring the referenced value exists.

CREATE TABLE Orders ( OrderID INT PRIMARY KEY, EmployeeID INT, CONSTRAINT FK_Orders_Employees FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) );

Unique (click to expand)

Ensures all values in a column (or combination) are distinct.

ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Email UNIQUE (Email);

Check (click to expand)

Validates that values satisfy a Boolean expression.

CREATE TABLE Products ( ProductID INT PRIMARY KEY, Price DECIMAL(10,2), CONSTRAINT CK_Products_Price CHECK (Price > 0) );

Not Null (click to expand)

Prevents null values in a column.

CREATE TABLE Departments ( DeptID INT PRIMARY KEY, DeptName NVARCHAR(100) NOT NULL );

Default (click to expand)

Specifies a default value for a column when none is supplied.

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_HireDate DEFAULT (GETDATE()) FOR HireDate;

Combined Example

This example demonstrates multiple constraints in a single table definition.

CREATE TABLE Customers ( CustomerID INT NOT NULL, Email NVARCHAR(255) NOT NULL, SignupDate DATE NOT NULL DEFAULT (GETDATE()), CONSTRAINT PK_Customers PRIMARY KEY (CustomerID), CONSTRAINT UQ_Customers_Email UNIQUE (Email), CONSTRAINT CK_Customers_Email CHECK (Email LIKE '%@%') );