SqlTutorial-1 : Lesson-2 : Class-5 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Integrity Constraints: CHECK Constraints for Custom Data Validations

CHECK Constraint ensures that all values in a column or group of columns satisfy certain predicate, pattern or both.

Unlike other Constraints, visibility of CHECK-Constraint is row not a column(s), i.e., using single CHECK-constraint you can validate multiple columns.

Syntax:

• Syntax1: Defining CHECK while Table Creation.

  CHECK ( logical_expression )
  
  
  • If the logical expression of CHECK evaluates to TRUE, the row will be inserted.
  • If the CHECK constraint expression evaluates to FALSE, the row insert will fail.

• Syntax2: For adding CHECK to existing table

  ALTER TABLE table_name
  WITH CHECK | WITH NOCHECK
    ADD CONSTRAINT constraint_name CHECK ( logical_expression )
  
  
  • WITH CHECK option (default): Existing data is validated against the new CHECK constraint.
  • WITH NOCHECK option: Skips validation for existing data, but new data(inserted or updated) Validated

Examples:

1. CHECK constraint as column constraint.

   CREATE TABLE Employee (
   -- Column Constraints. 
      emp_id int NOT NULL PRIMARY KEY DEFAULT 1000
         CHECK (emp_id BETWEEN 0 AND 1000),       -- Range validation
   
      emp_name varchar(30) NULL DEFAULT NULL
        CONSTRAINT no_names
        CHECK (emp_name NOT LIKE '%[0-9]%'),      -- Pattern Maching
   
      mgr_id int NOT NULL DEFAULT (1)
         REFERENCES employee(emp_id),             --Self Referencing.
   
      entered_date datetime NOT NULL
        CHECK (entered_date>=CONVERT(char(10),CURRENT_TIMESTAMP, 102))
         DEFAULT(CONVERT(char(10), GETDATE(), 102)), --DateValidation
   
      entered_by int NOT NULL DEFAULT SUSER_ID()
        CHECK (entered_by IS NOT NULL),
   
    -- Table Constraints. 
      CONSTRAINT valid_entered_by
        CHECK (entered_by=SUSER_ID() AND entered_by <> emp_id),
   
      CONSTRAINT valid_mgr
        CHECK (mgr_id <> emp_id OR emp_id=1),
   
      CONSTRAINT end_of_month
       CHECK (DATEPART(DAY, GETDATE()) < 28)
    )

2. CHECK constraint as Table constraints.

   CREATE TABLE customer(
      cust_no INTEGER NOT NULL,
      cust_group CHAR(3) NULL,
      CHECK (cust_group IN ('c1', 'c2', 'c10'))
   )
   // OR //
   
   CREATE TABLE MyTable(
      MyID Int IDENTITY(1, 1) NOT NULL PARIMARY KEY,
      MyDescription nVarChar(50) NOT NULL,
      Region nVarChar(10) NOT NULL,
      CONSTRAINT CK_Region 
         CHECK (Region IN('PNW','SW','MT','CENTRAL','EAST','SOUTH')) 
   )

3. Adding CHECK constraint to existing table.

   ALTER TABLE Employee WITH NOCHECK
      ADD CONSTRAINT CK_ContactType CHECK (First_Name NOT LIKE '%assistant%')

4. Disable a particular CHECK constraint of a table.

   ALTER TABLE employee NOCHECK CONSTRAINT CK_ContactType

5. Disable all CHECK constraint of a table.

   ALTER TABLE Employee NOCHECK CONSTRAINT ALL

6. Enable particular CHECK constraint on a table.

   ALTER TABLE employee CHECK CONSTRAINT CK_ContactType

7. Enable all check constraints of a table.

   ALTER TABLE employee CHECK CONSTRAINT ALL

Tricky CHECK constraint usage.

1. Validating a column as it contains no past date.

   CHECK (entered_date >=CONVERT(char(10), CURRENT_TIMESTAMP, 102))

2. Validating a column as it contains given length of values.

   CREATE TABLE MyTable (
      int1 int IDENTITY PRIMARY KEY,
      vch1 varchar(5) NOT NULL CHECK (LEN(vch1) > 0),
      vch2 varchar(5) NOT NULL CHECK (LEN(vch2) > 0)
   )

3. Validating a column as it contains values according to given pattern.

   ( Note pattern matching is based on _ , %, and [1-9], [a-b] but not regular expression based. )
   CHECK(name LIKE 'a%l_' )     // Agile is valid, Agreed invalid

4. Validating a column as it contains values as telephone number pattern.

   // For Phone char(15) NOT NULL
   ALTER TABLE Customers
       ADD CONSTRAINT CN_CustomerPhoneNo
           CHECK(Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

5. CHECK constraints with two conditions.

   CHECK ( pub_id in ('1389', '0736', '0877', '1622', '1756')
          OR
         pub_id like '99[0-9][0-9]' )

6. Multi-column CHECK

   CREATE TABLE ClassGrades(
      ClassID int,
      StudentID int,
      GradeLetter varchar(2),
      Constraint PK_ClassGrades PRIMARY KEY(ClassID, StudentID),
      Constraint CK_GradeRange_ClassID
        CHECK( LEFT(UPPER(GradeLetter),1) LIKE '[A-F]' AND ClassID<1000)
   )
   
   INSERT ClassGrades VALUES(1, 1, 'C+')
   INSERT ClassGrades VALUES(1, 2, 'A+')
   INSERT ClassGrades VALUES(1, 3, 'V-')
   INSERT ClassGrades VALUES(10, 1, 'A')
   INSERT ClassGrades VALUES(99, 2, 'A')