Constraints

 constraint with example in sql server

In Microsoft SQL Server, Constraints are rules applied on table columns to maintain data accuracy and integrity.

Types of Constraints in SQL Server

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

---

1. PRIMARY KEY Constraint

• Uniquely identifies each record.
• Does not allow NULL.

Example

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

---

2. FOREIGN KEY Constraint

• Creates relationship between two tables.

Example

CREATE TABLE Department
(
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employee
(
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(100),
    DeptID INT,
    
    CONSTRAINT FK_Employee_Department
    FOREIGN KEY (DeptID)
    REFERENCES Department(DeptID)
);

---

3. UNIQUE Constraint

• Prevents duplicate values.
• Allows one NULL value.

Example

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

---

4. NOT NULL Constraint

• Does not allow NULL values.

Example

CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

---

5. CHECK Constraint

• Restricts values based on condition.

Example

CREATE TABLE Employees
(
    EmpID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);

---

6. DEFAULT Constraint

• Sets default value automatically.

Example

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT GETDATE()
);

---

Using ALTER TABLE with Constraint

Add PRIMARY KEY

ALTER TABLE Students
ADD CONSTRAINT PK_Students PRIMARY KEY (StudentID);

Add CHECK Constraint

ALTER TABLE Employees
ADD CONSTRAINT CHK_Age CHECK (Age >= 18);

Add DEFAULT Constraint

ALTER TABLE Orders
ADD CONSTRAINT DF_OrderDate
DEFAULT GETDATE() FOR OrderDate;

---

Complete Example

CREATE TABLE Student
(
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Age INT CHECK (Age >= 18),
    City VARCHAR(50) DEFAULT 'Nagpur'
);

This table uses:

• PRIMARY KEY
• NOT NULL
• UNIQUE
• CHECK
• DEFAULT constraints together.