SQL Constraints

A set of rules are defined to enforce the database integrity, is called as Constraints.
The main purpose of constraint is to permit or prohibit the values in the columns.

Following are the Types of Constraint,

The above constraints are associated only with the tables.
These constraints are applied to the table at the time of table creation.

Primary Key	It identifies each row or record uniquely in a database table. It cannot have NULL values. A table can have only one primary key. It contains unique values. If a table has a primary key, then you cannot have two records of same value. For Example: Empid INT PRIMARY KEY, In the above example, there is one Employee table where Empid have a Primary Key. When multiple fields are used as a primary key, is called as Composite key. Syntax: PRIMARY KEY (column1, column2);
Foreign Key	Foreign key is a logical rule about values in multiple columns or in tables. It is a set of columns in a table. It requires to match at least one primary key of a row in another table. It defines the relationship between the tables. The 'references' keyword is used while referencing the column into the another table. For example: Empid INT PRIMARY KEY, FOREIGN KEY (Empid) references Department (Empid); In the above example, there are two tables Employee and Department. The 'Empid' in Employee table has a primary key. In Department table, it takes reference of 'Empid' from Employee table using Foreign key with the keyword 'references'.
Unique Key	It prevents two records from having identical values in a particular column. It identifies each record uniquely in a database table. Primary key and Unique key both provide a guarantee of uniqueness for a column. Primary key has a unique constraint automatically defined on it. Note: Important thing to understand about the primary key and unique key, is that a table can have many unique constraints, but only one primary key can have per table. Unique key is declared with 'NOT NULL' constraint at the time of creating a table. It accepts only one NULL value. It is a unique non-clustered index.
Check Constraint	It enables a condition to check the value being entered into a record. For example: Age INT NOT NULL CHECK (Age >= 19) Multiple Check constraints are used in a single column. It returns true or false result based on the logical operator according to Boolean (logical) expression.

Null

Null is a reserved keyword in SQL that indicates a data value does not exist in the database.
It identifies the Null special marker.
Null introduced by E. F. Codd, Creator of the relational database model.
A Null value indicates that the value is unknown, not applicable and has no value, but it does not mean that it has a zero value or a field which contains spaces.
It is used to represent a missing value.

For example:

In the above example, NOT NULL indicates that the column should accept explicit value of the given data type. There are two columns 'address' and 'phone_no' which are not assigned with the NOT NULL, which means that these columns could be NULL.
Null is not a data value, but it is a marker of an absent value.
It can cause problems when selecting data, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results.
IS NULL or IS NOT NULL is used to check for a NULL value.

Example of IS NOT NULL:

<employee> Table

empid	ename	age	city	phone_no
001	ABC	28	Pune	12345678
002	XYZ	30	Mumbai	54687901
003	PQR	35	Kolhapur

SELECT empid, ename, age, city, phone_no
FROM employee
WHERE phone_no IS NOT NULL;

Output:

empid	ename	age	city	phone_no
001	ABC	28	Pune	12345678
002	XYZ	30	Mumbai	54687901

Example of IS NULL:

SELECT empid, ename, age, city, phone_no
FROM employee
WHERE phone_no IS NULL;

Output:

empid	ename	age	city	phone_no
003	PQR	35	Kolhapur

All aggregate function (min, max, sum, count) ignores the NULL values except count() function.

When not to use NULL?

NULL is not specify explicitly in SELECT statement. For eg. SELECT NULL.
NULL is not specify explicitly as an operand of scalar expression.

For example,

NULL is not specify explicitly as an operand of a conditional expression.

For example,

« Previous

SQL Constraints

Tutorial

What is constraints?

Null

When not to use NULL?