Understanding Constraints in SQL Server

Constraints are rules enforced on data columns in a table. They are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. If any constraint violation occurs, the operation that caused it is aborted and an error is returned to the user.

Types of Constraints

1. PRIMARY KEY Constraint

Purpose

Ensures that each row in a table has a unique identifier. It also prevents NULL values.

Key Features

  • Uniquely identifies each record in a database table.
  • Cannot contain NULL values.
  • A table can have only one primary key.

Syntax Example

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

Alternatively, define a PRIMARY KEY constraint at the table level:

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    CONSTRAINT PK_Products PRIMARY KEY (ProductID)
);

2. FOREIGN KEY Constraint

Purpose

Ensures referential integrity between two tables. It links a column or set of columns in one table to the PRIMARY KEY or a UNIQUE constraint in another table.

Key Features

  • Establishes a relationship between tables.
  • Prevents actions that would destroy links between tables.
  • The column(s) in the referencing table must match the values in the referenced table's PRIMARY KEY or UNIQUE constraint.

Syntax Example

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
);

3. UNIQUE Constraint

Purpose

Ensures that all values in a column or set of columns are unique. Unlike PRIMARY KEY, it can allow one NULL value.

Key Features

  • Ensures uniqueness across rows for a column or set of columns.
  • Can contain NULL values (usually only one, depending on the RDBMS).
  • A table can have multiple UNIQUE constraints.

Syntax Example

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Email VARCHAR(100) UNIQUE
);

4. CHECK Constraint

Purpose

Enforces domain integrity by limiting the range of values that can be placed in a column.

Key Features

  • Restricts the values that can be entered into a column.
  • The condition defined in the CHECK constraint must evaluate to TRUE for the data to be accepted.

Syntax Example

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

5. DEFAULT Constraint

Purpose

Provides a default value for a column when no value is specified during an INSERT operation.

Key Features

  • Assigns a default value to a column if no value is provided.
  • Simplifies data entry and ensures data consistency.

Syntax Example

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    RegistrationDate DATE DEFAULT GETDATE()
);

6. NOT NULL Constraint

Purpose

Ensures that a column cannot have a NULL value.

Key Features

  • A column with the NOT NULL constraint must always contain a value.
  • Often used in conjunction with PRIMARY KEY or other essential columns.

Syntax Example

CREATE TABLE Tasks (
    TaskID INT PRIMARY KEY,
    TaskName VARCHAR(255) NOT NULL,
    DueDate DATE
);