Sql Server Constraints Primary Key Foreign Key Unique Check Default Complete Guide

 Last Update:2025-06-23T00:00:00     .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION.    7 mins read      Difficulty-Level: beginner

Understanding the Core Concepts of SQL Server Constraints Primary Key, Foreign Key, Unique, Check, Default

SQL Server Constraints: Primary Key, Foreign Key, Unique, Check, and Default

1. Primary Key Constraint

The Primary Key constraint uniquely identifies each record in a table. It must contain unique and non-null values, ensuring that no two records have the same key.

Syntax:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY
);

Example:

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

In the above example, EmployeeID is the primary key for the Employees table, ensuring that each employee ID is unique.

2. Foreign Key Constraint

The Foreign Key constraint creates a link between two tables, ensuring that the value in one table matches a value in another table. A foreign key constraint is used to enforce referential integrity.

Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    FOREIGN KEY (column2)
    REFERENCES referenced_table(column_in_referenced_table)
);

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    OrderDate DATE,
    FOREIGN KEY (EmployeeID)
    REFERENCES Employees(EmployeeID)
);

In this example, EmployeeID in the Orders table is a foreign key referencing EmployeeID in the Employees table, ensuring that only existing employee IDs can be used when placing an order.

3. Unique Constraint

The Unique constraint ensures that all values in a column are different. This constraint is similar to a primary key, except that a table can have multiple unique constraints, and it can contain a null value.

Syntax:

CREATE TABLE table_name (
    column1 datatype UNIQUE
);

Example:

CREATE TABLE Logins (
    LoginID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE
);

Here, Username must be unique across all entries in the Logins table. No two users can have the same username.

4. Check Constraint

The Check constraint ensures that all values in a column satisfy a specific condition. It limits the range of possible values for a column.

Syntax:

CREATE TABLE table_name (
    column1 datatype CHECK (condition)
);

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);

In this case, only customers 18 years or older can be inserted into the Customers table, as the Age column has a check constraint enforcing this rule.

5. Default Constraint

The Default constraint provides a default value for a column when no value is specified during insertion. It ensures that a column always has a value.

Syntax:

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value
);

Example:

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

Here, if the OrderDate is not specified when a new order is placed, it defaults to the current date and time, captured by the GETDATE() function.

Summary of Constraints in SQL Server

  • Primary Key: Ensures unique and non-null values in a column.
  • Foreign Key: Ensures the value in a column exists in another table.
  • Unique: Ensures values in a column are unique.
  • Check: Ensures column values meet a specific condition.
  • Default: Provides a default value for a column.

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement SQL Server Constraints Primary Key, Foreign Key, Unique, Check, Default

1. Primary Key Constraint

Purpose: A primary key uniquely identifies each row in a table. A table can have only one primary key, and it cannot contain NULL values.

Example:

Suppose we are creating a Employees table. We want to ensure that each employee has a unique EmployeeID.

Step-by-Step Example:

  1. Create the Table:

    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    BirthDate DATE
);

  2. Insert Data:

    INSERT INTO Employees (EmployeeID, LastName, FirstName, BirthDate)
VALUES (1, 'Doe', 'John', '1980-05-19');

  3. Attempt to Insert Duplicate Key:

    INSERT INTO Employees (EmployeeID, LastName, FirstName, BirthDate)
VALUES (1, 'Smith', 'Jane', '1985-06-22');
-- This will cause an error because EmployeeID 1 is already in use.

2. Foreign Key Constraint

Purpose: A foreign key ensures that the values in one table match those in another table. It maintains referential integrity.

Example:

We have an Orders table and an Employees table. Each order is assigned to an employee. We want to ensure that the EmployeeID in the Orders table exists in the Employees table.

Step-by-Step Example:

  1. Create the Employees Table (if not already created):

    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    BirthDate DATE
);

  2. Create the Orders Table with a Foreign Key:

    CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

  3. Insert Data into Employees and Orders:

    INSERT INTO Employees (EmployeeID, LastName, FirstName, BirthDate)
VALUES (1, 'Doe', 'John', '1980-05-19');

INSERT INTO Orders (OrderID, OrderDate, EmployeeID)
VALUES (101, '2023-04-15', 1);

  4. Attempt to Insert Invalid Data:

    INSERT INTO Orders (OrderID, OrderDate, EmployeeID)
VALUES (102, '2023-04-20', 99);
-- This will cause an error because EmployeeID 99 does not exist in the Employees table.

3. Unique Constraint

Purpose: A unique constraint ensures that all values in a column or a set of columns in a table are different. It can contain NULL values but only one NULL value in each column.

Example:

We want to ensure that the email addresses in our Users table are unique.

Step-by-Step Example:

  1. Create the Users Table:

    CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    Email VARCHAR(255) UNIQUE
);

  2. Insert Data:

    INSERT INTO Users (UserID, Username, Email)
VALUES (1, 'johndoe', 'johndoe@example.com');

  3. Attempt to Insert Duplicate Email:

    INSERT INTO Users (UserID, Username, Email)
VALUES (2, 'janedoe', 'johndoe@example.com');
-- This will cause an error because the email already exists.

4. Check Constraint

Purpose: A check constraint ensures that all values in a column satisfy a specific condition.

Example:

We want to ensure that the price in our Products table is always greater than 0.

Step-by-Step Example:

  1. Create the Products Table:

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

  2. Insert Valid Data:

    INSERT INTO Products (ProductID, ProductName, Price)
VALUES (1, 'Laptop', 999.99);

  3. Attempt to Insert Invalid Data:

    INSERT INTO Products (ProductID, ProductName, Price)
VALUES (2, 'Mouse', -5.99);
-- This will cause an error because the price is not greater than 0.

5. Default Constraint

Purpose: A default constraint provides a default value for a column when no value is specified during an insert.

Example:

For the Orders table, we want to set a default order date of today if no date is provided.

Step-by-Step Example:

  1. Create the Orders Table with Default Constraint:

    CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT GETDATE(),
    EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

  2. Insert Data without Specifying OrderDate:

Top 10 Interview Questions & Answers on SQL Server Constraints Primary Key, Foreign Key, Unique, Check, Default

1. What is a Primary Key in SQL Server?

Answer: A Primary Key is a constraint that uniquely identifies each record in a table. No two records can have the same primary key value, and the primary key column cannot accept NULL values. It ensures the data integrity in the table and speeds up the data retrieval.

2. How do you create a Primary Key in a table?

Answer: You can create a Primary Key during the creation of a table or adding it to an existing table. Here is an example for both:

During Table Creation:

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    EmployeeName varchar(50),
    Position varchar(30)
);

Adding Primary Key to Existing Table:

ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

3. What is the purpose of a Foreign Key in SQL Server?

Answer: A Foreign Key is a column or a combination of columns in a table that uniquely identifies a row in another table (the "referenced" table). Its purpose is to ensure referential integrity between tables; it maintains a relationship between the data in two tables.

4. How do you create a Foreign Key in SQL Server?

Answer: You can create a Foreign Key during or after the creation of a table. Here is an example for creating a Foreign Key in the same statement:

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate datetime,
    EmployeeID int,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

Adding Foreign Key to Existing Table:

ALTER TABLE Orders
ADD CONSTRAINT FK_Employee
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);

5. What is a Unique Key in SQL Server?

Answer: A Unique Key constraint ensures that all values in a column or a combination of columns are different (unique). Each table can have one or more unique keys. Like primary key constraints, unique key constraints do not accept NULL values unless specified otherwise.

6. How do you create a Unique Key in SQL Server?

Answer: To add a unique constraint to a table, you can define it while creating a table or using the ALTER TABLE statement. Here is how to do it:

During Table Creation:

CREATE TABLE Customers (
    CustomerID int UNIQUE,
    CustomerName nvarchar(100),
    Phone nvarchar(15)
);

Adding Unique Constraint to Existing Table:

ALTER TABLE Customers
ADD CONSTRAINT UC_CustomerPhone UNIQUE (Phone);

7. What is a Check Constraint in SQL Server?

Answer: A Check Constraint is a constraint that limits the possible values that can be inserted into a column. It is used to control the values in one or more columns based on specific criteria.

8. How do you create a Check Constraint in SQL Server?

Answer: Creating a Check Constraint can be done at the time of table creation or using the ALTER TABLE statement. Here are examples for both:

During Table Creation:

CREATE TABLE Products (
    ProductID int PRIMARY KEY,
    ProductName nvarchar(100),
    Price money CHECK (Price > 0)
);

Adding Check Constraint to Existing Table:

ALTER TABLE Products
ADD CONSTRAINT CHK_Price CHECK (Price > 0);

9. What is a Default Constraint in SQL Server?

Answer: A Default Constraint is a type of constraint used to insert a default value into a column. When an inserted row has no value for the column that has a default constraint, the SQL Server automatically inserts the default value into that column.

10. How do you create a Default Constraint in SQL Server?

Answer: A Default Constraint can be added during table creation or using the ALTER TABLE statement. Here are the examples:

During Table Creation:

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate datetime DEFAULT GETDATE(),
    EmployeeID int
);

Adding Default Constraint to Existing Table:

You May Like This Related .NET Topic

📥 Download Code ▶️ Watch Online 📖 Read Description

Login to post a comment.