Page 262 - Information_Practice_Fliipbook_Class11
P. 262
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,
Address VARCHAR(30),
City VARCHAR(20),
Pin_Code CHAR(6),
DOB DATE,
Salary INT NOT NULL DEFAULT 10000,
Dept_No SMALLINT
);#EMPLOYEE
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.
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)
248 Touchpad Informatics Practices-XI

