Page 260 - Information_Practice_Fliipbook_Class11
P. 260
Next, we give an example of a statement that creates an EMPLOYEE table with the following attributes: ID, FName,
LName, Gender, Address, City, Pin_Code, DOB, Salary and DeptNo. Also note that attribute ID is the
primary key of the table EMPLOYEE. When the primary key comprises a single attribute, often we use the keyword
PRIMARY KEY, following the type of the attribute to denote that it is the primary key of the table as shown below
in the CREATE TABLE statement:
CREATE TABLE: Used to specify
Table name
Attribute names, default values
Attribute types
Primary key
Constraints: NOT NULL, foreign key, values allowed
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,
Dept_No SMALLINT
); #EMPLOYEE
The primary key can also be specified after all attributes have been defined, as shown in the CREATE TABLE statement below:
CREATE TABLE EMPLOYEE (
ID INT NOT NULL UNIQUE,
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,
Dept_No SMALLINT,
PRIMARY KEY(ID)
); #EMPLOYEE
As ID is the primary key for the table EMPLOYEE, no two employees can have the same ID,i.e., ID must be
unique across the EMPLOYEE table. Further, as ID identifies an employee uniquely, this attribute cannot be NULL.
So, column constraints UNIQUE and NOT NULL are automatically applied to the attribute ID.
246 Touchpad Informatics Practices-XI

