SQL Server Constraints Primary Key, Foreign Key, Unique, Check, Default Step by step Implementation and Top 10 Questions and Answers
 Last Update: April 01, 2025      16 mins read      Difficulty-Level: beginner

SQL Server Constraints: A Comprehensive Overview

When designing databases, ensuring data integrity is paramount. SQL Server provides several constraints to enforce specific rules about the type of data that a column can contain and how various tables relate to one another. The primary constraints are Primary Key, Foreign Key, Unique, Check, and Default. Each of these plays a crucial role in maintaining the accuracy and reliability of the data.

1. Primary Key Constraint

The Primary Key constraint uniquely identifies each record in a table. Every row must have a unique primary key value, and it cannot contain NULL values. A table can have only one primary key. Here’s how to define a primary key:

  • Creating Table with a Primary Key:

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

  • Adding a Primary Key to an Existing Table:

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

Important Information:

  • The primary key enforces entity integrity by ensuring that each value in the primary key column is unique and not NULL.
  • It is used in conjunction with other tables to establish relationships between them.

2. Foreign Key Constraint

The Foreign Key constraint establishes a relationship between two tables by ensuring that values in a column (or columns) match the primary key values in another table. A table can have multiple foreign keys.

  • Creating Table with a Foreign Key:

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

  • Adding a Foreign Key to an Existing Table:

    ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Important Information:

  • A foreign key ensures referential integrity, ensuring that data in the foreign key column matches data in the primary key column of the referenced table.
  • If a record referenced by a foreign key is deleted or updated, the action can be cascaded or restricted based on the foreign key rule.

3. Unique Constraint

The Unique constraint ensures all values in a column (or a combination of columns) are unique. Unlike the primary key constraint, a column with a unique constraint can contain NULL values, but only one NULL value.

  • Creating Table with a Unique Constraint:

    CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

  • Adding a Unique Constraint to an Existing Table:

    ALTER TABLE Customers
ADD CONSTRAINT UQ_Email UNIQUE (Email);

Important Information:

  • A unique constraint is used to guarantee the uniqueness of a column while allowing NULL values.
  • This constraint is useful when you need to ensure uniqueness but do not need to enforce a primary key (e.g., email addresses).

4. Check Constraint

The Check constraint enforces data integrity by limiting the values that can be inserted into a column. It checks each value to ensure it meets a specific condition.

  • Creating Table with a Check Constraint:

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

  • Adding a Check Constraint to an Existing Table:

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

Important Information:

  • The check constraint ensures that data in a column meets a specific condition, enhancing data integrity.
  • Common uses include ensuring non-negative values, date ranges, or character length constraints.

5. Default Constraint

The Default constraint provides a default value for a column when no value is specified during the insertion of a new row.

  • Creating Table with a Default Constraint:

    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE DEFAULT GETDATE()
);

  • Adding a Default Constraint to an Existing Table:

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

Important Information:

  • The default constraint simplifies data entry by providing a default value.
  • It can be used for columns like timestamps, status indicators, or other default values, reducing the need for specifying them repeatedly.

Summary

SQL Server constraints are essential tools for maintaining data integrity and consistency. The Primary Key, Foreign Key, Unique, Check, and Default constraints each serve distinct purposes:

  • Primary Key: Ensures unique and non-NULL identifiers.
  • Foreign Key: Maintains referential integrity between tables.
  • Unique: Ensures unique values within a column.
  • Check: Limits values according to specified conditions.
  • Default: Provides default values for columns.

Understanding and appropriately applying these constraints can greatly enhance the robustness and reliability of your database designs.

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

SQL Server constraints play a crucial role in maintaining data integrity, ensuring that the data stored in a database is accurate, valid, and consistent. Here are some of the most frequently used constraints in SQL Server: Primary Key, Foreign Key, Unique, Check, and Default. This guide will walk you through examples, how to set up a route (create tables with constraints), and run the application with a focus on data flow, step by step.

Step 1: Set Route (Create Tables with Constraints)

Before delving into setting up constraints, let's first understand the entities we will be working with and how the data will flow between them.

Example Scenario: Imagine a simple online bookstore application. In this application, there are two primary entities: Books and Authors. Each book is written by an author, and each author can have written multiple books.

Entity Definition:

  • Authors: Contains information about authors (AuthorID, AuthorName, BirthDate).
  • Books: Contains information about books (BookID, Title, Genre, Price, AuthorID).

Data Flow: Data flows from the Authors table to the Books table through the AuthorID which is foreign key in the Books Table. This relationship ensures that if a book exists, it is written by an author who is already recorded in the Authors table.

Step 2: Create Tables with Constraints

a. Authors Table

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,    -- Primary Key constraint
    AuthorName NVARCHAR(100) NOT NULL,
    BirthDate DATE
);
  • Primary Key Constraint: Ensures that each author has a unique ID and does not allow NULL values.
  • NOT NULL Constraint: Ensures the AuthorName cannot be NULL.

b. Books Table

CREATE TABLE Books (
    BookID INT PRIMARY KEY IDENTITY,  -- Primary Key constraint with identity
    Title NVARCHAR(100) NOT NULL,
    Genre NVARCHAR(50),
    Price DECIMAL(10, 2),
    AuthorID INT,                    -- Foreign Key constraint
    CONSTRAINT FK_Books_Authors FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
    CONSTRAINT CHK_Price CHECK (Price > 0),  -- Check Constraint
    ISBN NVARCHAR(13) UNIQUE         -- Unique constraint
);
  • Primary Key Constraint: Ensures each book has a unique identifier (BookID). The IDENTITY keyword automatically generates a unique value whenever a new record is inserted.
  • Foreign Key Constraint: Ensures that AuthorID in the Books table refers to an existing AuthorID in the Authors table, maintaining referential integrity.
  • Check Constraint: Ensures that the price of a book cannot be negative.
  • Unique Constraint: Ensures that ISBN numbers are unique across all books.

Step 3: Run the Application and Understand Data Flow

Let's walk through inserting data into these tables, paying close attention to how data integrity constraints work.

a. Inserting Data into Authors Table

INSERT INTO Authors (AuthorID, AuthorName, BirthDate) 
VALUES (1, 'J.K. Rowling', '1965-07-31');

b. Inserting Data into Books Table

INSERT INTO Books (Title, Genre, Price, AuthorID, ISBN) 
VALUES ('Harry Potter and the Philosopher\'s Stone', 'Fantasy', 19.99, 1, '9780747532743');

In the Books table:

  • The BookID is automatically generated.
  • The Title and Price fields are checked for NOT NULL (for Title) and the price should be greater than zero (as per the Check Constraint).
  • The AuthorID is verified to ensure there is a corresponding entry in the Authors table.
  • The ISBN number must be unique and not previously existing.

c. Data Flow Explanation

  1. Authors Table Insertion: The application begins by inserting a new author into the Authors table with a unique AuthorID and valid AuthorName and BirthDate.
  2. Books Table Insertion: After inserting the author, the application proceeds to insert a new book into the Books table, including its Title, Genre, and Price. It also references an existing author via AuthorID.
  3. Constraints Enforce Data Integrity: Throughout both insertions, constraints are automatically checked to ensure:
    • No duplicate AuthorID or BookID.
    • No null Title or AuthorID.
    • All Price values are positive.
    • ISBN numbers remain unique.

d. Handling Constraint Violations

  • Primary Key Violation: Attempting to insert a duplicate AuthorID will result in an error.
  • Foreign Key Violation: Attempting to insert a Book with an AuthorID that doesn't exist in the Authors table will result in an error.
  • Unique Constraint Violation: Inserting a duplicate ISBN number will result in an error.
  • Check Constraint Violation: Inserting a negative price will result in an error.

Conclusion

Using SQL Server constraints ensures the integrity and consistency of your data within your database. By setting up tables with appropriate constraints like Primary Key, Foreign Key, Unique, Check, and Default, you can automatically detect and prevent data issues. The step-by-step example provided demonstrates how to create tables with constraints and insert data while adhering to these rules, ensuring data flow is seamless and error-free.

Understanding these constraints is essential for any database designer or developer, helping to build robust and reliable applications.

Top 10 Questions and Answers on SQL Server Constraints: Primary Key, Foreign Key, Unique, Check, Default

1. What is a Primary Key in SQL Server, and how is it defined?

Answer: A Primary Key in SQL Server uniquely identifies each record in a table. It must contain unique values and cannot have NULLs. A Primary Key can be a single column or a combination of multiple columns (composite key). You can define a Primary Key when creating a table using the PRIMARY KEY constraint or by adding it using an ALTER TABLE statement.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    DepartmentID INT
);

-- Adding Primary Key using ALTER TABLE
CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName NVARCHAR(100),
    DepartmentID INT
);

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

2. Can a table have more than one Primary Key?

Answer: No, a table can only have one Primary Key. However, the Primary Key can be composed of multiple columns (composite Primary Key).

Example:

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

3. What role does a Foreign Key play in SQL Server, and how is it created?

Answer: A Foreign Key is a column, or a set of columns, in one table that uniquely identifies a row of another table. This establishes a link between the data in two tables, ensuring data integrity and consistency. Foreign Keys are used to enforce referential integrity constraints between tables.

Example:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

4. What are the differences between a Primary Key and a Foreign Key in SQL Server?

Answer: The main differences are:

  • Uniqueness: A Primary Key ensures that all values are unique and not NULL within the table. A Foreign Key does not need to be unique but must reference a unique value in the parent table.
  • Table Reference: A Primary Key is unique to the table it is defined on, whereas a Foreign Key creates a link between the table it is defined on and another table.
  • Null Values: Primary Keys cannot have NULLs, but Foreign Keys can have NULLs unless specified otherwise with the NOT NULL constraint.

5. How do you create a Unique Constraint in SQL Server, and what is its purpose?

Answer: A Unique Constraint ensures that all values in a column (or a combination of columns) are unique, similar to a Primary Key, but it does allow for NULL values (only one NULL value per column in single-column constraints). It's used to enforce uniqueness of a column (or a group of columns) without setting it as the Primary Key.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Email NVARCHAR(100),
    UNIQUE (Email)
);

-- Adding Unique Constraint using ALTER TABLE
ALTER TABLE Employees
ADD CONSTRAINT UQ_Email UNIQUE (Email);

6. What is a Check Constraint in SQL Server, and how is it used?

Answer: A Check Constraint is used to enforce domain integrity by limiting the values that are accepted by one or more columns. It checks inserted or updated values to ensure they meet specific conditions.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    Salary DECIMAL(10,2),
    CHECK (Salary > 0)
);

-- Adding Check Constraint using ALTER TABLE
ALTER TABLE Employees
ADD CONSTRAINT CHK_Salary CHECK (Salary > 0);

7. Can a Check Constraint reference a column in another table?

Answer: No, a Check Constraint cannot directly reference columns in another table. It can only check the values within the same table.

8. What is a Default Constraint in SQL Server, and how is it applied?

Answer: A Default Constraint provides a default value for a column when no value is specified during an insert operation. It ensures that a column always has a value.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    HireDate DATE DEFAULT GETDATE()
);

-- Adding Default Constraint using ALTER TABLE
ALTER TABLE Employees
ADD CONSTRAINT DF_HireDate DEFAULT GETDATE() FOR HireDate;

9. Can a column have multiple constraints in SQL Server?

Answer: Yes, a single column can have multiple constraints. For example, a column can have both a Primary Key and a Check Constraint.

Example:

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

10. What are the benefits of using constraints in SQL Server?

Answer: The benefits of using constraints in SQL Server include:

  • Data Integrity: Ensures that only valid data is entered into the tables.
  • Consistency: Maintains consistent data across the database.
  • Performance: Helps the SQL Server engine in optimizing queries and improving performance.
  • Error Prevention: Prevents the entry of erroneous or duplicate data, which can save significant time during troubleshooting and debugging.

By leveraging these constraints effectively, developers can create robust and reliable databases that maintain data integrity and consistency throughout their application lifecycle.