INDEXES:

Consider an example of library, and you have to search for a book say “Chicken Soup for Soul”. If the library is not organized then just imagine what would you do, how would you start your search, where to first start. But if the data is organized like for example categorized may be on Starting letter or Author or Type of book, atleast you know from where to search.

Same thing applies to INDEX in databases. Indexes are used to find rows with specific column values quickly. Without an Index, SQL would have to start search from first row and then read the entire table.  This would take lot of time, of the data or table is huge. With Index, SQL could start the search from that point and save time. We can say Index is a table of pointers.  Each row in Index table points to a row in the data table.

CREATE INDEX:

Indexes can be created using CREATE INDEX statement. This statement enables you to add INDEXes to existing tables.  Multiple column index can also be created of form (col1, col2,col3,….colN). The Index values are formed by concatenating the values of the given columns.

Syntax:

     CREATE INDEX index_name ON table_name;

There are different types of Indexes:

1. Single Column Index:

A single column Index is created using only 1 column on the table.

    CREATE INDEX index_name ON table_name (column_name);

2.  Multi Column Index:

Multi column Index is created by using two or more columns in a table. 

    CREATE INDEX index_name ON table_name (col1, col2, col3,…colN);

3.  Unique Index:

A unique Index is used when you do not want to allow duplicate values in the table for that column. 

     CREATE UNIQUE INDEX index_name ON table_name (column_name);

DROP INDEX command:

INDEX can be dropped using DROP command.

Syntax:

      DROP INDEX index_name;

Points to remember while using Indexes:

1. It is a good idea to use Indexes on fields that are frequently used to access records. As this would speed up the retrieval.
2. Do not create Indexes for fields that you will never use for retrieval of records. This will save the wastage of space.
3. Do not create Indexes for fields that do not differentiate one record from other.
4. It doesn’t make sense of using Indexes to small tables. Use Index only when data is large.

Constraints:

Constraints are the rules that determine what values the table attributes can have.  By applying constraints you can prevent users from entering invalid data into that column. Suppose for example of Employee and Department, if there are only 10 departments in the company you can constrain the system or database into accepting only those 10 department values. The user will not be able to proceed until he/she enters correct department.

There are different types of constraints:

1. Column level constraint:

Column level constraint is the limit applied to a column.  These can be applied while creating table.

NOT NULL is a type of column level constraint. It does not allow user to leave that column blank. The user has to enter value that satisfies the data type.

UNIQUE is another constraint that can be applied to a column.  It specifies that values entered to the column should be unique.

CHECK constraint, checks for the condition for the column. It will only accept values that satisfy the CHECK. For example:

Here if entered SALE_PRICE value is not  >0 and <100 then that value will not be entered in the column.

2. Table Constraint

Table constraints are the type of constraints that are applicable to whole table.

Different types of table constraints are PRIMARY KEY and FOREIGN KEY.

Primary key uniquely identifies each row/column of a table.

Foreign key uniquely identifies row/column of two or more tables. These keys are used to maintain referential integrity of the tables. Once you add the constraint, you cannot delete table unless you have cleared all the referential data of all the tables. Even while updating data, the data should follow the referential integrity. Hence  the data duplication is avoided and sanity of data is maintained.

These constraints are also added during CREATion of table.

Adding constraints to table:

 As discussed above, you can add constraints while creation of table or  later by using ALTER table:

Syntax:

     ALTER TABLE table_name

     ADD CONSTRAINT constraint_name constraint-type;

DROP Constraints:

You can drop constraints using ALTER table statement as below:

     ALTER TABLE table_name

     DROP CONSTRAINT constraint_name;

While deleting the constraints, you have to ensure that data integrity is maintained.