Constraints In SQL Server

Constraints In SQL: Top 5 Best Usage

by

Constraints in SQL Server are essential to ensuring data integrity between tables and enhancing the performance of a user database. In this article, we thoroughly review these constraints, their various benefits, best practices, and commonly asked questions.

Introduction

Data Integrity and accuracy are essential features in relational databases. Constraints in SQL Server are crucial features that allow us to achieve the required objectives per the organization’s needs. A constraint in any database is a rule or set of rules that applies to data in a table to fulfil users’ requirements and guarantee that only valid and consistent data is stored. Constraints enable developers and database managers to eliminate errors, improve performance, and simplify data validation processes.

SQL Server offers various constraints, including Primary Key, Foreign Key, Unique, Check, and Default, each with a specific function in ensuring relational database integrity. These constraints work at the table level, providing a declarative method for enforcing business rules and data connections directly within the database structure.

A glimpse into history

The concept of constraints first appeared with early relational databases to satisfy the growing need for data accuracy. SQL Server gradually added many limitations to optimize database management and increase efficiency.

Types of Constraints in SQL Server

Microsoft SQL Server offers several constraints designed to enforce specific rules and ensure data integrity within a database. Below are the primary types of Constraints in SQL:

Primary Key Constraint

Purpose: Ensures a column (or a combination of columns) must contain unique and non-null values.

Use Case: Used to identify each record in a table uniquely.

Advantages of Primary Key Constraint:
  • Guarantees a unique identity for every record.
  • Automatically creates a clustered index, improving query performance.
  • Prevents duplicate and null values.
Disadvantages of Primary Key Constraint:
  • Only one primary key is allowed per table.
  • Modifying or dropping a primary key can be complex in large tables.

Example:

Items table Without Primary Key

CREATE TABLE Items (
    ItemID INT,
    ItemName VARCHAR(100),
    ItemPrice decimal(10,2)
);
go
INSERT INTO Items(ItemID, ItemName, ItemPrice)
SELECT 1001,'Cello-Ball Pen', 10.00 UNION ALL
SELECT 1002,'Cello-Gel Pen', 10.00 UNION ALL
SELECT 1003,'Link-Ball Pen', 10.00 UNION ALL
SELECT 1001,'Cello-Ball Pen', 10.00 UNION ALL
SELECT 1004,'link-Gel Pen', 10.00
go
SELECT * FROM Items
Items table Without Primary Key
Items table With Primary Key

CREATE TABLE Items (
    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(100),
    ItemPrice decimal(10,2)
);
go
INSERT INTO Items(ItemID, ItemName, ItemPrice)
SELECT 1001,'Cello-Ball Pen', 10.00 UNION ALL
SELECT 1002,'Cello-Gel Pen', 10.00 UNION ALL
SELECT 1003,'Link-Ball Pen', 10.00 UNION ALL
SELECT 1001,'Cello-Ball Pen', 10.00 UNION ALL
SELECT 1004,'link-Gel Pen', 10.00
go
SELECT * FROM Items
Primary key violation error at time of Insert

Foreign Key Constraint

Purpose: Enforces a link between the data in two tables by referencing a column in another table.

Use Case: Maintains referential integrity between tables.

Advantages of Foreign Key Constraint:
  • It maintains data consistency between related tables.
  • It prevents the deletion of referenced data without first addressing dependencies.
  • It helps in establishing parent-child relationships.
Disadvantages of Foreign Key Constraint:
  • It can slow down insert and update operations due to referential checks.
  • It requires careful management of cascading operations like ON DELETE CASCADE.

Example:

Sales table without Foreign Key Constraint

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ItemID INT
);

Without a relation between parent & child tables, the system will allow duplicate data in the child table. To avoid this, we must create a relation between parent & child tables using Foreign Key…..References. An example is given below for clarity:

Sales table with Foreign Key Constraint

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ItemID INT,
    FOREIGN KEY (ItemID) REFERENCES Items(ItemID)
);

Unique Constraint

Purpose: Ensures all values in a column (or a combination of columns) are unique.

Use Case: Prevents duplicate entries in a column.

Advantages of Unique Constraint:
  • It allows for multiple unique constraints in a table.
  • It enforces uniqueness without the limitations of a primary key.
Disadvantages of Unique Constraint:
  • Automatically creates a non-clustered index, which may consume additional resources.
  • It does not allow null duplicates; for multiple null values, other solutions are needed.

Example:

Vendor table without Unique constraint

CREATE TABLE Vendors (
    VendorID INT PRIMARY KEY,
    VendorEmail VARCHAR(100) UNIQUE
);

Without a Unique constraint, the system will not restrict the user from entering duplicate data in the table. We must put Unique limitations on a particular column or columns to avoid this. An example is given below for clarity:

Vendor table with Unique constraint

CREATE TABLE Vendors (
    VendorID INT PRIMARY KEY,
    VendorEmail VARCHAR(100) UNIQUE
);

Check Constraint

Purpose: Limits the values that can be placed in a column by enforcing a condition.

Use Case: Ensures that only valid data is entered based on specified criteria.

Advantages of Check Constraint:
  • It allows complex validations at the database level.
  • It reduces the need for application-level validations.
Disadvantages of Check Constraint:
  • This can lead to performance overhead for large datasets.
  • Complex conditions may require additional debugging during errors.

Example:

Items Table without CHECK constraint

CREATE TABLE Items (
    ItemID INT PRIMARY KEY,
    ItemPrice DECIMAL(15, 2)
);

With the CHECK constraint, we can restrict the user from putting valid data in the table during the insert command execution. It’ll help to avoid garbage data in the table. An example is given below for clarity:

Items Table without CHECK constraint

CREATE TABLE Items (
    ItemID INT PRIMARY KEY,
    ItemPrice DECIMAL(15, 2),
    CHECK (ItemPrice > 10)
);

Default Constraint

Purpose: Automatically assigns a default value to a column if no value is provided.

Use Case: Simplifies data entry by providing default values.

Advantages of Default Constraint:
  • Simplifies data entry by auto-populating fields.
  • Ensures consistency when no value is provided.
Disadvantages of Default Constraint:
  • It can cause issues if default values are not meaningful.
  • Altering default values for existing data requires additional steps.

Example:

Sales table without DEFAULT Constraint

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleDate DATETIME
);

Using DEFAULT Constraint, we can set the default value for a column. Suppose the user is not passing the required value; the system will use the default value & insert it into the column. It’ll help to avoid null in the table. An example is given below for clarity:

Sales table without DEFAULT Constraint

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    SaleDate DATETIME DEFAULT GETDATE()
);

Not Null Constraint

Purpose: Ensures that a column cannot contain NULL values.

Use Case: Guarantees that critical fields always have value.

Advantages of Not Null Constraint:
  • Ensures critical fields always have value.
  • Improves data integrity by preventing incomplete records.
Disadvantages of Not Null Constraint:
  • It cannot be used in situations where null values are valid inputs.
  • It requires restructuring of business logic to allow nulls.

Example:

Students table without NOT NULL constraint

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName NVARCHAR(50)
);

Suppose, as per the requirement, we want to restrict the end user from putting some value for a partial column, so use this constraint. An example is given below for clarity:

Students table with NOT NULL constraint

CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    AccountName NVARCHAR(50) NOT NULL
);

FAQs (Frequently Asked Questions)

Q: What are the SQL constraints?

Ans: Constraints are restrictions that ensure that a database contains only valid data.

Q: What are the different types of SQL constraints?

Ans: Key types include primary, foreign, unique, check, and default.

Can limitations boost performance?

Ans: Yes, they increase query performance with indexed constraints.

Q: How can I adjust SQL Server constraints?

Ans: To modify constraints, use the ALTER TABLE statement.

Q: Are restrictions required in SQL Server?

Ans: No, although they are highly recommended for data integrity.

Q: Can limitations be turned off?

Ans: Yes, restrictions can be temporarily deactivated using NOCHECK.

Q: How do foreign key limitations work?

Ans: They verify that a column’s value matches a value from another table.

Q: What happens when a constraint fails?

Ans: The operation is terminated, and an error is returned.

Q: Can we have several constraints on the same column?

Ans: Several constraints, such as UNIQUE and CHECK, can be imposed.

Q: Are constraints recorded in system tables?

Ans: Yes, metadata for constraints are stored in system views.

Conclusion

SQL Create Constraints are critical for maintaining data integrity and improving database performance. Understanding their kinds, applications, and best practices allows you to maximize their potential for building robust and efficient databases.

Review the below articles:

Dbcc Freeproccache: A powerful command

Extended Events in SQL Server: A Deep Dive

SQL Server Database Mail

Query Store: A Powerful Tool

Understand Deadlocks in SQL Server

DBCC SQLPerf (LogSpace): Top 15 Usage

Explore DQS in SQL Server

Leave a Comment