Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 21 - Modular_V2.0_SQL_Flipbook
P. 21

    CourseID INTEGER PRIMARY KEY, CourseName CHAR(50),
                            StudentID INTEGER,   -- Foreign Key

                            FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
                         );
                     In this example, the Students table has StudentID as its primary key which uniquely identifies each

                     student while the Courses table contains a StudentID column that acts as a foreign key as it is
                     referencing the StudentID in the Students table.
                     Composite Primary Key

                     When a table requires unique identification for each row but one column is unable to fulfill the
                     requirement of uniqueness, in that case two or more columns can be used for making a primary key.

                         CREATE TABLE Students
                         (
                           Student_name CHAR(30), Father_name CHAR(30), Marks INT, PRIMARY KEY( Student_
                         name, Father_name)
                         );

                     This creates a composite candidate key where name and father name together defines a unique
                     record in the table.
                     Unique Constraint

                     The UNIQUE constraint ensures that each value in a column is unique. The UNIQUE and PRIMARY
                     KEY constraints both ensure that a column or set of columns is unique. A UNIQUE constraint is
                     automatically added to a PRIMARY KEY constraint. A unique key however allows one NULL value in
                     the column, it is applied to.

                     For example, the following SQL command creates a UNIQUE constraint on the StudentID column
                     when the Students table is created:

                         CREATE TABLE Students
                         (
                         StudentID  INTEGER,  FirstName  CHAR(30), LastName  CHAR(30),  Marks  INT,
                         UNIQUE(StudentID)
                         );
                     Default Constraint

                     The DEFAULT constraint is used to specify a default value for a column. If no other value is specified,
                     the default value will be added to all new records.

                     For example, the following SQL command sets a DEFAULT value for the Marks column when the
                     Students table is created:
                         CREATE TABLE Students

                         (
                         StudentID INTEGER, FirstName CHAR(30), LastName CHAR(30), Marks INT DEFAULT
                         33
                         );

                                                                                                                         19
                                                                                                   Introduction to MySQL

| 16 17 18 19 20 21 22 23 24 25 26 |