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
   145   146   147   148   149   150   151   152   153   154   155