Page 152 - Informatics_Practices_Fliipbook_Class12
P. 152

While inserting a tuple into the table EMPLOYEE, if the user does not provide any value for the attribute Salary,
        then  it  will  be  set  as  10000.  The  default  value  of  the  character  type  must  be  enclosed  within  single  quotes  or
        double-quotes.

        4.4.4 CHECK constraint
        The CHECK constraint restricts the values that an attribute can take.  For example, it would be reasonable to limit the
        values of Gender to 'M', 'F', and 'O', indicating male, female, and other.  A CHECK constraint to achieve
        this may be included as part of the statement specified as follows:

               CHECK(Gender = 'M' OR Gender = 'F' OR Gender = 'O')
        The revised CREATE TABLE statement for the table  EMPLOYEE would appear as follows:
        CREATE TABLE EMPLOYEE (

               ID INT PRIMARY KEY,
               FName VARCHAR(20) NOT NULL,
               LName VARCHAR(20) NOT NULL,
               Gender CHAR(1) NOT NULL

        CHECK(Gender = 'M' OR Gender = 'F' OR Gender = 'O'),
               Address VARCHAR(30),
               City VARCHAR(20),
               Pin_Code CHAR(6),
               DOB DATE,

               Salary INT NOT NULL DEFAULT 10000,
               Dept_No SMALLINT
        );#EMPLOYEE
        CHECK constraint may also be used to specify that an attribute would take one of the several specified values or a
        numeric value in a specified range. For example, the constraint to perform a check on valid values of Gender may also
        be written as follows:

               CHECK(Gender IN ('M', 'F', 'O'))
        As another example, Salary may be restricted to lie between 8,000 and 1,00,000 as follows:

               CHECK(Salary BETWEEN 8000 AND 100000)
        Alternatively, CHECK constraints may be may be described separately as a table level constraints, after all, attributes
        are described, for example:

        CREATE TABLE EMPLOYEE (
               ID INT PRIMARY KEY,

               FName VARCHAR(20) NOT NULL,
               LName VARCHAR(20) NOT NULL,
               Gender CHAR(1) NOT NULL,
               Address VARCHAR(30),

               City VARCHAR(20),
               Pin_Code CHAR(6),
               DOB DATE,
               Salary INT NOT NULL,


          138  Touchpad Informatics Practices-XII
   147   148   149   150   151   152   153   154   155   156   157