Page 173 - IT-802_class_12
P. 173

1.5.4 KEY Constraint

            There are two ways to specify a table’s primary key. If a single column makes up the table’s primary key, that column
            can be declared the primary key along with its description. Primary keys are NOT NULL by default; therefore, this
            constraint doesn’t need to be specified explicitly. If we want to make the Roll_No column the primary key, we can do
            so in the following way:

            CREATE TABLE STUDENT
            (
            Roll_No iNTEGER pRimARy KEy,

            Name VARCHAR(30),
            Gender CHAR(1),

            Date_of_Birth DATE,
            Address VARCHAR(50)
            );
            However, if the primary key consists of many attributes, each attribute must be listed separately, enclosed in parenthesis,
            and separated by commas. If we want to make the primary key by combining Roll_No and Date_of_Birth columns, we
            can do so in the following way:

            CREATE TABLE STUDENT
            (

            Roll_No iNTEGER,
            Name VARCHAR(30),
            Gender CHAR(1),

            Date_of_Birth DATE,
            Address VARCHAR(50),

            pRimARy KEy (Roll_No, Date_of_Birth)
            );
            Now, if we see the structure of the table STUDENT, the following output appears:
            +---------------+-------------+------+-----+---------+-------+
            | Field         | Type        | Null | Key | Default | Extra |
            +---------------+-------------+------+-----+---------+-------+
            | Roll_No       | int         | NO   | pRi | NULL    |       |
            | Name          | varchar(30) | yES  |     | NULL    |       |
            | Gender        | char(1)     | yES  |     | NULL    |       |
            | Date_of_Birth | date        | NO   | pRi | NULL    |       |
            | Address       | varchar(50) | yES  |     | NULL    |       |
            +---------------+-------------+------+-----+---------+-------+
            1.5.5 REFERENTIAL Integrity Constraint

            This constraint is added into a table by using the FOREIGN KEY clause. This clause contains the foreign key and the
            primary key referred to by this foreign key along with the name of the table. Let us consider the EMPLOYEE and
            DEPARTMENT tables:

            CREATE TABLE EmpLOyEE

            (
            Emp_iD iNTEGER pRimARy KEy,
                                                                                 Database Concepts—RDBMS Tool   171
   168   169   170   171   172   173   174   175   176   177   178