SQL Constraints

SQL Constraints

SQL constraints are used to specify rules for data in tables.

If there is any data behavior that violates constraints, the behavior will be terminated by the constraint.

Constraints can be specified when creating the table (via the CREATE TABLE statement), or after the table is created (via the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
    column_name1 data_type(size) constraint_name,
    column_name2 data_type(size) constraint_name,
    column_name3 data_type(size) constraint_name,
    ....
);

In SQL, we have the following constraints:

  • NOT NULL- Indicates that a column cannot store NULL values.
  • UNIQUE- Ensures that each row in a column must have a unique value.
  • PRIMARY KEY- A combination of NOT NULL and UNIQUE. Ensures that a column (or a combination of two or more columns) has a unique identifier, helping to find a specific record in a table more easily and quickly.
  • FOREIGN KEY- Ensures referential integrity by making data in one table match values in another table.
  • CHECK- Ensures that values in a column meet specified conditions.
  • DEFAULT- Specifies a default value when no value is assigned to a column.
  • INDEX- Used for fast access to data in database tables.

1. NOT NULL

Ensures that a column cannot have NULL values.

Example

CREATE TABLE Students (
    StudentID INT NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50),
    Age INT
);

2. UNIQUE

Ensures that all values in a column are unique.

Example

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

3. PRIMARY KEY

Uniquely identifies each row record in a table. The PRIMARY KEY constraint is a combination of NOT NULL and UNIQUE.

Example

CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    OrderNumber INT NOT NULL,
    OrderDate DATE NOT NULL
);

4. FOREIGN KEY

Ensures that values in one table match values in another table, thereby establishing a relationship between the two tables.

Example

CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    OrderNumber INT NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

5. CHECK

Ensure that values in a column meet specific conditions.

Example

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

6. DEFAULT

Sets a default value for a column.

Example

CREATE TABLE Customers (
    CustomerID INT NOT NULL PRIMARY KEY,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50),
    JoinDate DATE DEFAULT GETDATE()
);

7. INDEX

Used for fast access to data in database tables.

CREATE INDEX idx_lastname ON Employees (LastName);

Comprehensive Example

Example

CREATE TABLE Students (
    StudentID INT NOT NULL PRIMARY KEY,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18),
    Email VARCHAR(100) UNIQUE,
    EnrollmentDate DATE DEFAULT GETDATE()
);

Through these constraints, the database management system can ensure the consistency, integrity, and accuracy of data.

In the following sections, we will explain each constraint in detail.

Other Extensions