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


4 comments:

  1. Such a great articles in my carrier, It's wonderful commands like easiest understand words of knowledge in information's.
    SQL Server Training in Chennai

    ReplyDelete

  2. It is very good blog and useful for students and developer ,Thanks for sharing
    .Net Online Training
    Dot Net Online Training Bangalore
    .Net Online Course

    ReplyDelete

  3. It is very good blog and useful for students and developer ,Thanks for sharing
    .Net Online Training
    Dot Net Online Training Bangalore
    .Net Online Course

    ReplyDelete