Page 150 - Informatics_Practices_Fliipbook_Class12
P. 150
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.
A table may have more than one attribute as the primary key, collectively known as composite key. In such case, the
primary key is defined after the attributes of the table have been defined, as shown in the example below:
CREATE TABLE WORKS_ON (
Proj_No SMALLINT NOT NULL,
Emp_Id INT NOT NULL,
Hours SMALLINT,
PRIMARY KEY(Proj_No, Emp_Id)
);#WORKS_ON
The statement given above creates the WORKS_ON table that has a composite primary key consisting of attributes,
Proj_No and Emp_Id..
136 Touchpad Informatics Practices-XII

