Page 261 - Information_Practice_Fliipbook_Class11
P. 261

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..


                   While writing SQL statements, the symbol # marks the beginning of a comment that extends to the end of the line. A
                   C-style comment is enclosed between /* and */. A comment may also begin with —- (double hyphen), followed by a
                   whitespace or control character such as space, tab, or end of line (EOL),  and extends to the end of the line.

            The primary key can be defined as a constraint as shown below:

            CONSTRAINT EMPLOYEE_Key PRIMARY KEY(ID)
            Syntax for the constraint statement is given below:

            CONSTRAINT ConstraintName PRIMARY KEY(attribute_1, [attribute_2],...)
            Note that the primary key constraint includes the name of at least one attribute included in the primary key. In general,
            several attributes may be included in the primary key constraint.

            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,

                CONSTRAINT EMPLOYEE_Key PRIMARY KEY(ID)

            ); #EMPLOYEE
            Note that the EMPLOYEE_Key is the name of the primary key constraint on ID. A named constraint may be modified
            or dropped later by referring to its name.

            Next, we discuss some more SQL constraints:
            DEFAULT constraint

            A default value for an attribute may be specified using the DEFAULT constraint. For example, if most of the employees
            have a salary of  10000, it would be wise to set it as the default salary for an employee as follows:
            Salary INT NOT NULL DEFAULT 10000


                                                                Database Concepts and the Structured Query Language  247
   256   257   258   259   260   261   262   263   264   265   266