SQL Constraints

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level. Column level constraints are applied only to one column, whereas
table level constraints are applied to the whole table.

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 - Prevents actions that would destroy links between tables
**CHECK - Ensures that the values in a column satisfies a specific condition
**DEFAULT - Sets a default value for a column if no value is specified
**INDEX - Used to create and retrieve data from the database very quickly

NOT NULL:

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to
define such constraint on this column specifying that NULL is now not allowed for that column.
A NULL is not the same as no data, rather, it represents unknown data.

For example, the following SQL creates a new table called CUSTOMER and adds five columns, three of which,
ID and NAME and AGE, specify not to accept NULLs:

UNIQUE:

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.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

For example, the following SQL creates a new table called CUSTOMER and adds five columns. Here, AGE
column is set to UNIQUE, so that you can not have two records with same age:

PRIMARY KEY:

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values.A primary key column cannot have NULL values.
A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used
as a primary key, they are called a composite key.If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s).

Here is the syntax to define ID attribute as a primary key in a CUSTOMER table.

FOREIGN KEY:

A foreign key is a key used to link two tables together.This is sometimes called a referencing key.Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.If a table has a primary key defined on any field(s),then you can not have two records having the same value of that field(s).

Consider the structure of the two tables as follows:

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.

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.

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, SALARY
column is set to 5000.00 by default, so in case INSERT INTO statement does not provide a value for this column.
then by default this column would be set to 5000.00.

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.

To create an INDEX on AGE column, to optimize the search on customers for a particular age, following is the SQL
syntax: