Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

CHECK Constraint

The CHECK constraint in SQL Server is used to limit the values that can be inserted into a column. It enforces a condition that must be met for the data to be valid. By using CHECK constraints, you can prevent invalid data from being entered into your tables, thereby preserving the accuracy and reliability of your data.

Key Concepts

1. Purpose of `CHECK` Constraint

Ensures data integrity by validating the data against specified conditions.
Useful for enforcing rules such as value ranges, data formats, or relationships between columns.

2. Syntax

CHECK (condition): Defines the condition that must be met for the data to be valid.

3. Adding `CHECK` Constraints

You can add CHECK constraints when creating a table or modify an existing table to include a CHECK constraint.

4. Combining Multiple Conditions

Multiple conditions can be combined using logical operators such as AND and OR.

Code Samples

1. Creating a Table with `CHECK` Constraints

Define a table with various CHECK constraints to enforce data rules.

CREATE TABLE Store(
StoreID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
StoreName VARCHAR(100) NOT NULL, -- Store name, cannot be NULL
Location VARCHAR(100) NULL, -- Location can be NULL
OpenDate DATETIME, -- OpenDate can be NULL
CHECK (LEN(StoreName) > 0) -- StoreName must not be empty

);

CREATE TABLE Products(
ProductID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
ProductName VARCHAR(100) NOT NULL, -- Product name, cannot be NULL
Category VARCHAR(50) NULL, -- Category can be NULL
Price DECIMAL(10,2) NOT NULL, -- Price cannot be NULL
StockQuantity INT NULL, -- StockQuantity can be NULL
CHECK (Price >= 0), -- Price must be non-negative
CHECK (StockQuantity >= 0) -- StockQuantity must be non-negative
);

CREATE TABLE Customer(
CustomerID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
FirstName VARCHAR(50) NOT NULL, -- First name, cannot be NULL
LastName VARCHAR(50) NOT NULL, -- Last name, cannot be NULL
Email VARCHAR(100) NULL, -- Email can be NULL
PhoneNumber VARCHAR(15) NULL, -- PhoneNumber can be NULL
CHECK (LEN(FirstName) > 0), -- FirstName must not be empty
CHECK (LEN(LastName) > 0) -- LastName must not be empty
);

CREATE TABLE Sales(
SaleID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
SaleDate DATETIME NOT NULL, -- SaleDate, cannot be NULL
StoreID INT NOT NULL, -- Foreign key, cannot be NULL
ProductID INT NOT NULL, -- Foreign key, cannot be NULL
CustomerID INT NULL, -- CustomerID can be NULL
Quantity INT NOT NULL, -- Quantity, cannot be NULL
TotalAmount DECIMAL(10, 2) NOT NULL, -- TotalAmount, cannot be NULL
CHECK (Quantity > 0 AND TotalAmount > 0) -- Quantity and TotalAmount must be positive
);

CREATE TABLE Sales(
SaleID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
SaleDate DATETIME NOT NULL, -- SaleDate, cannot be NULL
StoreID INT NOT NULL, -- Foreign key, cannot be NULL
ProductID INT NOT NULL, -- Foreign key, cannot be NULL
CustomerID INT NULL, -- CustomerID can be NULL
Quantity INT NOT NULL, -- Quantity, cannot be NULL
TotalAmount DECIMAL(10, 2) NOT NULL, -- TotalAmount, cannot be NULL
CHECK (Quantity > 0 AND TotalAmount > 0) -- Quantity and TotalAmount must be positive

);

Combined Code Sample

These examples illustrate how to use CHECK constraints to enforce data integrity in your SQL Server tables. You can modify these examples to fit your specific data validation needs.

Tansy SQL Course - CHECK Constraint - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found