Following are the types of constraints:

·   PRIMARY KEY: This key is the combination of UNIQUE KEY and NULL KEY. This key ensures that a column must have a unique identity and it cannot be left blank.

·  UNIQUE: This key ensures each row must have unique value.

·  NOT NULL: This key indicates that a column cannot be left blank.

·  CHECK:  This key checks the column meets some specific condition.

·   FOREIGN KEY: It ensures referential integrity of the record in one table to match values in another table.

·   DEFAULT: This key specify a default value for a column.

NOT NULL

Example:

 In the above example, student id and Name cannot be left blank where as address and city can be left blank.

 UNIQUE CONSTRAINTS

The UNIQUE constraint ensures that each record in a database table must be unique. The UNIQUE and PRIMARY KEY both ensures uniqueness for a column or a set of column. A PRIMARY KEY automatically has a UNIQUE constraint define on it.

The difference between PRIMARY and UNIQUE constraint is that a table can have many UNIQUE constraint, but only one PRIMARY KEY. Following is the example of UNIQUE constraint:

PRIMARY KEY CONSTRAINTS

The PRIMARY KEY uniquely identifies record in a table. PRIMARY KEY ensures that these records must be unique in a database table. Each table can only have ONE PRIMARY KEY and that column cannot be left blank or cannot contain NULL values. Following is the example of PRIMARY KEY constraint:

 FOREIGN KEY CONSTRAINTS

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let’s illustrate with the help of an example:

Student_info table:

 Marks table:

In the above example, two tables are given: student_info and marks

Studentid is PRIMARY KEY for student_info table and FOREIGN KEY for marks table.

The FOREIGN KEY prevents action that would destroy links between tables and also prevents invalid data from being inserted into the foreign key column.

CHECK CONSTRAINTS

The CHECK constraint ensures the limit on value range that can be placed in a column. When CHECK constraint is applied on single column it allows only certain values for this column. Following is the example of CHECK constraint:

 DEFAULT CONSTRAINTS

 CREATE INDEX

CREATE INDEX is used to create an index.  An index is used to find the data quickly and efficiently.

Note: When updating a table with index, it takes more time then without index. So, only index need to be created on fields. Following is the example of CREATE INDEX:

 DROP INDEX

Index, table and database can be removed using DROP index. Following is the example of DROP index:

DROP INDEX name_of_index  ON  name_of_table

 ALTER TABLE

The ALTER statement is used to delete, add, update table. Following is an example of ALTER TABLE:

 AUTO INCREMENT

AUTO_INCREMENT generates unique value when a new record is inserted. Following is the example of AUTO INCREMENT:

CREATE TABLE Persons
(

ID int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

)