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

