In SQL (Structured Query Language), data validation refers to the process used to confirm the accuracy and reliability of data. Because databases are often updated, deleted, queried, or migrated by multiple users or programs, it is critical to maintain the integrity of the data. In the following content, we will introduce how to implement some basic validation rules in SQL to ensure the quality of data.

Unique constraint

We usually use UNIQUE constraint on ID type columns. For example, create a book table and specify that the ISBN of each book must be unique and cannot be empty:

CREATE TABLE Books (

    ISBN VARCHAR(13) NOT NULL UNIQUE,

    Title NVARCHAR(100),

    Author NVARCHAR(100),

    PublicationYear INT

);

In this example, the ISBN field is set to UNIQUE and NOT NULL, ensuring that each book has a unique International Standard Book Number and that the number cannot be left blank.

Check constraints

A check constraint consists of a logical expression that determines which values are valid. A simple example is in a payroll database where we want to specify the maximum value that can be entered. The syntax for a CHECK constraint when creating a table is as follows.

CREATE TABLE table_name

(

column1 datatype [ NULL | NOT NULL ],

column2 datatype [ NULL | NOT NULL ],



...

CONSTRAINT constraint_name

CHECK (column_name condition)

);

Therefore, we can create a table of employee salaries and impose a check constraint on the values entered into the Salary column:

CREATE TABLE dbo.EmployeeSalaries (

    EmployeeID int PRIMARY KEY,

    EmployeeType int,

    Salary decimal(9,2),

    CONSTRAINT CK_EmployeeSalaries_SalaryRange 

    CHECK (EmployeeType = 1 AND Salary >= 0 AND Salary <= 200000.00)

);

If we need to add a check constraint to a column in an existing table, we can use the ALTER statement:

ALTER TABLE dbo.EmployeeSalaries

ADD CONSTRAINT CK_EmployeeSalaries_SalaryRange

CHECK (EmployeeType = 1 AND Salary >= 0 AND Salary <= 200000.00);

To drop a check constraint, we can use the following command:

ALTER TABLE dbo.EmployeeSalaries

DROP CONSTRAINT CK_EmployeeSalaries_SalaryRange;

Finally, it is often useful to temporarily enable or disable a check constraint, which we can do as follows:

To enable a check constraint:

ALTER TABLE dbo.EmployeeSalaries

WITH CHECK CHECK CONSTRAINT CK_EmployeeSalaries_SalaryRange;

To disable a check constraint:

ALTER TABLE dbo.EmployeeSalaries

NOcheck CONSTRAINT CK_EmployeeSalaries_SalaryRange;

As you can see, check constraints are easy to create and flexible to use.

Summary

Performing data validation in SQL is essential to maintaining the integrity and consistency of your database. Using SQL Server Management Studio (SSMS), you can implement data validation by creating and managing constraints. This includes using NOT NULL constraints to prevent null values from being inserted, using UNIQUE constraints to ensure uniqueness of a column or combination of columns, and using CHECK constraints to restrict the range or format of values in a column. Through these methods, you can effectively control data quality at the database level.

Community

🐦