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.

Microsoft SQL Server Training Online Learning Classes Integrity Constraints CHECK Constraint

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')


5 comments: