SQL Constraints

What are Constraints in SQL?

Suppose we are creating a table to store the data of people. We need a primary key that must be unique and should not contain any null value. This will help to uniquely identify records, it will also help in faster retrieval of queries. So, we have to use some constraints like PRIMARY KEY constraint.

In simple terms, constraints mean some kind of restrictions. So, the constraints in SQL are nothing but a set of rules that are enforced on the relational database tables. Formally, we can say that constraints are the set of rules imposed on the column(s) of a table to restrict the data that can be inserted into a given column.

For Example, we can set a DEFAULT value for the "sample_name" column when the "sample" table is created:

Query:

Syntax of Constraints in SQL

As we have discussed, constraints can be imposed at two different times namely - at the time of creation and after creation. Let's see the syntax of constraints one by one.

1. Constraint imposed at the time of table creation using CREATE TABLE command: Syntax

Example:

We are imposing a UNIQUE constraint on the sample-number column of the sample table.

2. Constraint imposed after the time of table creation using ALTER TABLE command: Syntax:

Example:

We are imposing a UNIQUE constraint on the sample-number column of the sample table. We have to use MODIFY because we add a constraint after the creation of the table.

Types of Constraints in SQL

Constraints can be applied either on the table or a specific column. The constraints applied on the table are called Table level constraints on the other hand, the constraints applied on columns are called Column level constraints. Some of the most commonly used constraints are discussed below:

1. NOT NULL Constraint

NOT NULL is a column-level constraint that is imposed on a single column. NOT NULL constraint is used to ensure that the constrained column should not contain any NULL value in any row or tuple. NOT NULL is usually used in a situation where we do not want to add any tuple in the table without providing a value to the constrained column.

The NOT NULL constraint should be enforced on the column that is supposed to contain important information which may be further used to extract query results.

Example - Primary Key columns should not contain a null value.

Syntax:

For example, Let's create a Person table with "ID", and "Name" columns not accepting NULL values when the "Persons" table is created. As discussed earlier, we can impose constraints in two ways. Let's see their Query:

1. CREATE TABLE

2. ALTER TABLE command:

2. UNIQUE Constraint

UNIQUE constraint is a column-level constraint that is used to ensure that the constrained column should only contain a unique value(or value should be different) in the specified column. It can contain a null value. UNIQUE Constraint is usually used to uniquely identify each tuple in the table.

A UNIQUE constraint should be enforced on the column that is supposed to contain some ID kind of thing that cannot be the same for more than one.

For example – The roll number column in a student table must be UNIQUE.

One important thing to note with the UNIQUE constraint is that - Primary Keys are always unique(by default) but the UNIQUE constrained column may or may not be a Primary Key.

For example, Let's create a Person table with the "ID" column as a UNIQUE column. This means that the ID column will only contain distinct values. As discussed earlier, we can impose constraints in two ways. Let's see their Query:

1. CREATE TABLE

2. ALTER TABLE command:

3. PRIMARY KEY Constraint

PRIMARY KEY constraints say that the field in the table should not be NULL, and it should be unique as well. It is a column-level constraint. Primary Key constraint is used to ensure that the constrained column should have unique and NOT NULL values. The primary key has automatically UNIQUE and NOT NULL constraints applied to it. It is usually used to index the table or uniquely identify each tuple in the table.

The PRIMARY KEY constraint should be enforced on the column that is supposed to be the primary key of the table. Primary keys help to retrieve query results from a table.

For example – The roll number column in a student table can be made the primary key as every student has a roll number(NOT NULL) and no two students can have the same roll number(UNIQUE constraint).

We can say that:
PRIMARY KEY CONSTRAINT = UNIQUE CONSTRAINT + NOTNULL CONSTRAINT

For example, Let's create a Person table with "ID" column as a PRIMARY KEY column. As discussed earlier, we can impose constraints in two ways. Let's see their Query:

1. CREATE TABLE

2. ALTER TABLE command:

4. FOREIGN KEY Constraint

Foreign Keys or referencing keys are used to relate two or more tables. So FOREIGN KEY constraint prevents operations that can destroy the link between the tables. Foreign Keys are used in situations where we do not want the deletion of data from one table to hamper the data in the other table.

One important thing to note here is that - the Foreign Key of one table points to or references the Primary Key of the second table. So, when we delete data from the main table that points to data in the other table, we will be shown the error - Record in child table exists.

For example - Suppose we are storing student details on one table and their family details on the other table. So, the Foreign Key of the student table will point to the Primary Key of Family details table.

For example, Let's take two tables for reference:

Person table

Order table

"O-ID" column in the "Order" table points to the "P-ID" column in the "Person" table. The "P-ID" column in the "Person" table is the PRIMARY KEY in the "Person" table. The "P-ID" column in the "Order" table is the FOREIGN KEY in the "Order" table. As discussed earlier, we can impose constraint on Order table in two ways. Let's see their Query:

1. CREATE TABLE

2. ALTER TABLE command:

5. CHECK Constraint

CHECK constraint is a column-level constraint that says that the field in the table should satisfy the specified condition. It is used to ensure that the constrained column should satisfy a certain condition before the entry of the data into the column. It specifies a requirement that must be met by each row in a database table.

For example, Let's create a Person table with CHECK constraint on the "Age" column. The CHECK constraint ensures that the age of every person must be 60, or older. As discussed earlier, we can impose constraints in two ways. Let's see their Query:

1. CREATE TABLE

2. ALTER TABLE command:

6. DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column if no value is provided. We can specify a null or some other value. Default constrained value ensures that the constrained column should not be left bank, the default value will be added to all the new records if no value is provided.

For example, Let's create a Person table with DEFAULT constraint on the "Country" column. The DEFAULT constraint puts "India" as the default country if the user has not provided any country. As discussed earlier, we can impose constraints in two ways. Let's see their Query:

1. CREATE TABLE

2. ALTER TABLE command:

7. CREATE INDEX Constraint

CREATE INDEX constraint is used to create indexes on a table. Indexes help in the faster retrieval of data queries. We can create UNIQUE indexes or indexes containing duplicate values as per our query frequency and type of data.

Indexing is used to quickly retrieve particular data from the database. Formally we can define Indexing as a technique that uses data structures to optimize the searching time of a database query. Indexing reduces the number of disks required to access a particular data by internally creating an index table.

For example, Let's create a Person table and add INDEX constraint on the ID column so that we can achieve faster query results in case of large data set of persons. As discussed earlier, we can impose constraints in two ways. Let's see their Query:

Need for SQL Constraints

Data and its security and maintenance are a great concern for database administrators. They use different types of constraints to maintain database consistency.

Constraints help us to achieve:

• Constraints help the database administrator to maintain the accuracy and reliability of the data in the table.

For example, the administrator can use NOT NULL constraint on a column that is not supposed to contain a null value.

• Constraints help to maintain the integrity of the data during the operations performed on the table.

For example, the administrator can use PRIMARY KEY constraint on a column so that the user cannot enter a value that is there already in the database.

• Constraints also help to enforce limits on the input so that the operation does not lead to abortion.

For example, the administrator can use a CHECK constraint (like a data type) on a column so that the user can only input a specified type of data. Otherwise, the database may get damaged.

How to Create Constraints in SQL?

We can impose constraints on the table either at the time of the creation of the table or after the creation of the table.

There are two ways of imposing Constraints in SQL:

1. At the time of creation, we only must add the constraint’s name to impose the specified constraint.
2. After the creation, we need to use ALTER TABLE and then the constraint's name.