MYSQL Tutorial Part 5

 CONSTRAINTS:

    SQL constraints are used to specify rules for data in a table.

CREATE CONSTRAINTS:

    Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

    Syntax

        CREATE TABLE table_name ( column1 datatype constraint,column2 datatype constraint);

    The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value

• UNIQUE - Ensures that all values in a column are different

• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

• FOREIGN KEY - A key in one table, that refers to the primary key of another table.

• CHECK - Ensures that the values in a column satisfy a specific condition

• DEFAULT - Sets a default value for a column if no value is specified

• CREATE INDEX - Used to create and retrieve data from the database very quickly

NOT NULL:

• By default, a column can hold NULL values.
• The NOT NULL constraint enforces a column to NOT accept NULL values.
• This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

        Syntax

            CREATE TABLE table_name ( column1 datatype NOT NULL);

UNIQUE KEY:

• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
• Can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
• It accepts NULL values

        Syntax:  

            CREATE TABLE table_name ( column1 datatype, UNIQUE(column1);

PRIMARY KEY:

• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key.

        Syntax:  

            CREATE TABLE table_name ( column1 datatype, PRIMARY KEY(column1));

FOREIGN KEY:

• It is like a child table. It refers only to primary key column values from another table. 
• We can't insert any values directly in a child table without referring primary key table. 
• We can't delete/update values directly in the primary key table if it refers to foreign key values.

        Syntax:

            CREATE TABLE table_name ( column1 datatype, FOREIGN KEY(column1) REFERENCES  table2(primary-key column));

CHECK:

• The CHECK constraint is used to limit the value range that can be placed in a column.
• If you define a CHECK constraint on a column it will allow only certain values for this column.
• If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

        Syntax:

            CREATE TABLE table_name ( column1 datatype,  CHECK(column name condition);

Note: The latest version of MySQL won't support 'check'. It was used in the first version.

DEFAULT:

• The DEFAULT constraint is used to set a default value for a column.
• The default value will be added to all new records if no other value is specified.
  
  Example
  
       CREATE TABLE Students(rollno int NOT NULL, name varchar(50), age int, department varchar(30), district varchar(40) DEFAULT 'Chennai', fees int, PRIMARY KEY(rollno), UNIQUE(name), CHECK (age>=18));

CREATE INDEX:

• The CREATE INDEX statement is used to create indexes in tables.
• Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

        CREATE INDEX Syntax

            Creates an index on a table. Duplicate values are allowed:

            CREATE INDEX index_name ON table_name (column1, column2, ...);

        CREATE UNIQUE INDEX Syntax

            Creates a unique index on a table. Duplicate values are not allowed:

            CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

AUTO_INCREMENT:

• Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
• Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
• By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

            Syntax:

                CREATE TABLE tablename(column1 datatype AUTO_INCREMENT, PRIMARY KEY (column1));

            Example
                CREATE TABLE Students(rollno int AUTO_INCREMENT, PRIMARY KEY(rollno))

CREATE VIEW:

• In SQL, a view is a virtual table based on the result set of an SQL statement.
• A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
• You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.
• A view is created with the CREATE VIEW statement.

        Syntax

            CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

        Example
            CREATE VIEW [Chennai Students] AS SELECT name, department FROM Students WHERE district = 'Chennai';

                                                Thanks for choosing our blog 😊. 

                                          Will discuss the Clause in the next part. -  Part-6

Tutorial Topics:

Introduction  -  Part-1

Database Creation  -  Part-2

Aggregate Functions  - Part-3

Conditions  - Part-4

Constraints  - Part-5

Clauses  -  Part-6

Joins  -  Part-7

Tutorial Topics for examples:

Database Creation -  Part-1

Table Modifications  -  Part-2

Clauses   - Part-3

Operators  - Part-4

Joins  - Part-5