Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 22 - Modular_V2.0_SQL_Flipbook
P. 22

    Check Constraint

                  The CHECK constraint restricts the value range that can be placed in a column. For example, the following
                  SQL command creates a CHECK constraint on the Marks column when the Students table is created.

                  The CHECK constraint ensures that the marks of students must  be 33, or greater.  The CHECK
                  constraint rejects the operation with an error.
                         CREATE TABLE Students
                                                                Hands On
                       (
                       StudentID INTEGER,                        Create  a  Books table  with  BookID,  Title,
                       FirstName CHAR(30),
                                                                 Author,  and  Price columns. Set  BookID as
                       LastName CHAR(30), Marks
                                                                 the PRIMARY KEY.
                       INT, CHECK(Marks>=33)
                       );

                       MODIFYING A TABLE

                  Modifying a table means making a change in the base structure of the table. For example, after
                  creating a table, you realise that you have skipped adding a column named Address to the table
                  or you want to change the data type of a column or you want to remove a column from the table.
                  MySQL provides the ALTER TABLE command to modify an existing table.

                  Adding a Column

                  SQL provides ALTER TABLE command with ADD COLUMN command to add a column in an existing
                  table. Syntax to add a column is as follows:

                       ALTER TABLE table
                       ADD [COLUMN] column_name datatype [FIRST|AFTER existing_column];

                  Where, FIRST|AFTER clause allows to specify the position of the column to be added. If you don’t
                  explicitly specify the position of the new column, MySQL will add it as the last column. For example,
                  adding the Address column in the Students table:
                       ALTER TABLE Students
                       ADD COLUMN Address VARCHAR(50);

                  The preceding command will add the Address column as the last column in the Students table. To
                  check the added column, SQL provides the DESC command. The DESC command describes the
                  structure of the table. For example,
                       DESC Students;

                  The DESC command will show the following output:
                                +-----------+-------------+------+-----+---------+-------+
                                | Field     | Type        | Null | Key | Default | Extra |
                                +-----------+-------------+------+-----+---------+-------+
                                | StudentID | int         | YES  |     | NULL    |       |
                                | FirstName | char(30)    | YES  |     | NULL    |       |
                                | LastName  | char(30)    | YES  |     | NULL    |       |
                                | Marks     | int         | YES  |     | NULL    |       |
                                | Address   | varchar(50) | YES  |     | NULL    |       |
                                +-----------+-------------+------+-----+---------+-------+

                    20
                          Touchpad MODULAR (Ver. 2.0)

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