9. Check Constraint

•  A type of constraint that specify a condition that must be true for each row in the table.
• Defined using a Boolean Expression
• If the expression evaluates to true, the row is considered valid and can be inserted or updated
• E.g. to make sure inserted data is positive, is of specified length, is between given range, etc. check constraint can be used.

Syntax

• While Creating the table

                CREATE TABLE table_name (

                column1 datatype  ,

                column2 datatype,

                ...,

                CONSTRAINT constraint_name CHECK (condition)

                );

    E.g.

            CREATE TABLE employees (

            employee_id int NOT NULL,

            first_name varchar(50) NOT NULL,

            last_name varchar(50) NOT NULL,

            salary decimal(10,2) NOT NULL,

            CONSTRAINT salary_check CHECK (salary > 0)

            );

• After the Creation of the table 

            ALTER TABLE table_name

            ADD CONSTRAINT constraint_name CHECK (condition);

    E.g.    

            ALTER TABLE employees

            ADD CONSTRAINT salary_check CHECK (salary > 0);

• To Modify the constraint 

            ALTER TABLE table_name

            DROP CONSTRAINT constraint_name,

            ADD CONSTRAINT constraint_name CHECK (condition);

    E.g.

            ALTER TABLE employees

            DROP CONSTRAINT salary_check,

            ADD CONSTRAINT salary_check CHECK (salary >= 0);