Page 174 - IT-802_class_12
P. 174

Emp_Name VARCHAR(30) ,

        Address VARCHAR(30)
        );

        CREATE TABLE DEpARTmENT
        (
        Dept_iD iNTEGER pRimARy KEy,

        Emp_No iNTEGER,
        Designation VARCHAR(15),
        FOREiGN KEy (Emp_No) REFERENCES EmpLOyEE(Emp_iD)

        );
        Now, if you see the structure of the DEPARTMENT table, the following output appears:
        +-------------+-------------+------+-----+---------+-------+
        | Field       | Type        | Null | Key | Default | Extra |
        +-------------+-------------+------+-----+---------+-------+
        | Dept_iD     | int         | NO   | pRi | NULL    |       |
        | Emp_No      | int         | yES  | mUL | NULL    |       |
        | Designation | varchar(15) | yES  |     | NULL    |       |
        +-------------+-------------+------+-----+---------+-------+
        As you can see in the above table structure, the key column for Emp_No contains MUL which indicates multiple
        occurrences of a given value can appear within the column. This is also because it is a foreign key.
                     Notes



                    The referenced table must be created before it is referred in any other table.



        You can also specify the action to be taken in case the foreign key attribute or the primary key attribute value is
        changed, as that may result in a violation of the referential integrity constraint.

          1.6 naMing of constRaint

        The CREATE TABLE command allows for the naming of constraints. The advantage is that using the ALTER TABLE command,
        specified constraints can be quickly modified or eliminated. When naming a constraint, use the keyword CONSTRAINT,
        then the constraint’s name and its definition. For example,
        CREATE TABLE STUDENT

        (
        Roll_No iNTEGER,
        Name VARCHAR(30),

        Gender CHAR(1),
        Date_of_Birth DATE,
        Address VARCHAR(50),

        CONSTRAiNT STUDENT_pK pRimARy KEy (Roll_No, Date_of_Birth)
        );
        In the above table, the primary key constraint is named as STUDENT_PK.



          172   Touchpad Information Technology-XII
   169   170   171   172   173   174   175   176   177   178   179