Check Constraint

In SQL Server, constraints are rules applied to columns in a table to enforce data integrity and consistency. Two important types of constraints are the CHECK constraint and the DEFAULT constraint. Here are detailed explanations of each, with examples:

CHECK Constraint

The CHECK constraint is used to limit the values that can be entered into a column. It ensures that all values in a column satisfy a specific condition.

Example 1: Ensuring Positive Age

CREATE TABLE Persons (

PersonID INT PRIMARY KEY,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Age INT,

CONSTRAINT CHK_Age CHECK (Age >= 0)

);

• This CHECK constraint ensures that the Age column only contains non-negative values.

Example 2: Validating Salary Range

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Salary DECIMAL(10, 2),

CONSTRAINT CHK_Salary CHECK (Salary BETWEEN 30000 AND 200000)

);

• This CHECK constraint ensures that the Salary column only contains values between 30,000 and 200,000.

Take Charge of Your Data Skills - Join Our SQL Server Developer Course at TrainingHub.io!

DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during an insert.

Example 1: Default Hire Date

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

HireDate DATE DEFAULT GETDATE()

);

• This DEFAULT constraint sets the HireDate to the current date if no date is provided during insertion.

Example 2: Default Status

CREATE TABLE Orders (

   OrderID INT PRIMARY KEY,

   OrderDate DATE DEFAULT GETDATE(),

   Status NVARCHAR(20) DEFAULT 'Pending'

);

• This DEFAULT constraint sets the Status column to 'Pending' if no status is specified during insertion.

TrainingHub.io provides hands-on Data Analyst courses in Toronto, perfect for both beginners and experienced professionals.

Key Differences and Use Cases

Purpose:

• CHECK constraints enforce specific rules for the data that can be entered into a column.
• DEFAULT constraints provide default values for columns when no value is provided.

Enforcement:

• CHECK constraints prevent invalid data from being inserted.
• DEFAULT constraints automatically insert predefined values when no value is provided.

Combined Example

Combining both CHECK and DEFAULT constraints in a single table definition:

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

ProductName NVARCHAR(100) NOT NULL,

Price DECIMAL(10, 2) CHECK (Price >= 0),

StockQuantity INT DEFAULT 0 CHECK (StockQuantity >= 0)

);

In this Products table:

• The Price column has a CHECK constraint ensuring it is non-negative.
• The StockQuantity column has a DEFAULT constraint setting its default value to 0, and a CHECK constraint ensuring it is non-negative.

Altering Existing Tables
You can add CHECK and DEFAULT constraints to existing tables using the ALTER TABLE statement.

Adding a CHECK Constraint

ALTER TABLE Employees

ADD CONSTRAINT CHK_Age CHECK (Age >= 18);

• This adds a CHECK constraint to ensure that Age is at least 18.

Adding a DEFAULT Constraint

ALTER TABLE Orders

ADD CONSTRAINT DF_OrderDate DEFAULT GETDATE() FOR OrderDate;

• This adds a DEFAULT constraint to set the OrderDate to the current date if no date is provided.

Using CHECK and DEFAULT constraints effectively ensures data integrity and consistency, helping to enforce business rules directly within the database schema.

Learn, Practice, Excel - Join Our SQL Server Developer Course! Choose TrainingHub.io, the best IT training institute in Canada, to receive quality education and industry-recognized certifications.