Constraints in SQL

Constraints in SQL means we are applying certain conditions or restrictions on the database. This further means that before inserting data into the database, we are checking for some conditions. If the condition we have applied to the database holds true for the data which is to be inserted, then only the data will be inserted into the database tables.

Constraints in SQL can be categorized into two types:

  1. Column Level Constraint:
    Column Level Constraint is used to apply a constraint on a single column.
  2. Table Level Constraint:
    Table Level Constraint is used to apply a constraint on multiple columns.

Some of the real-life examples of constraints are as follows:

  1. Every person has a unique email id. This is because while creating an email account for any user, the email providing services such as Gmail, Yahoo or any other email providing service will always check for the availability of the email id that the user wants for himself. If some other user already takes the email id that the user wants, then that id cannot be assigned to another user. This simply means that no two users can have the same email ids on the same email providing service. So, here the email id is the constraint on the database of email providing services.
  2. Whenever we set a password for any system, there are certain constraints that are to be followed. These constraints may include the following:
    • There must be one uppercase character in the password.
    • Password must be of at least eight characters in length.
    • Password must contain at least one special symbol.

Constraints available in SQL are:

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK
  6. DEFAULT
  7. CREATE INDEX

Now let us try to understand the different constraints available in SQL in more detail with the help of examples. We will use MySQL database for writing all the queries.

1. NOT NULL

  • NULL means empty, i.e., the value is not available.
  • Whenever a table's column is declared as NOT NULL, then the value for that column cannot be empty for any of the table's records.
  • There must exist a value in the column to which the NOT NULL constraint is applied.

NOTE: NULL does not mean zero. NULL means empty column, not even zero.

Syntax to apply the NOT NULL constraint during table creation:

Example:

Create a student table and apply a NOT NULL constraint on one of the table's column while creating a table.


Constraints in SQL

To verify that the not null constraint is applied to the table's column and the student table is created successfully, we will execute the following query:


Constraints in SQL

Syntax to apply the NOT NULL constraint on an existing table's column:

Example:

Consider we have an existing table student, without any constraints applied to it. Later, we decided to apply a NOT NULL constraint to one of the table's column. Then we will execute the following query:


Constraints in SQL

To verify that the not null constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

2. UNIQUE

  • Duplicate values are not allowed in the columns to which the UNIQUE constraint is applied.
  • The column with the unique constraint will always contain a unique value.
  • This constraint can be applied to one or more than one column of a table, which means more than one unique constraint can exist on a single table.
  • Using the UNIQUE constraint, you can also modify the already created tables.

Syntax to apply the UNIQUE constraint on a single column:

Example:

Create a student table and apply a UNIQUE constraint on one of the table's column while creating a table.


Constraints in SQL

To verify that the unique constraint is applied to the table's column and the student table is created successfully, we will execute the following query:


Constraints in SQL

Syntax to apply the UNIQUE constraint on more than one column:

Example:

Create a student table and apply a UNIQUE constraint on more than one table's column while creating a table.


Constraints in SQL

To verify that the unique constraint is applied to more than one table's column and the student table is created successfully, we will execute the following query:


Constraints in SQL

Syntax to apply the UNIQUE constraint on an existing table's column:

Example:

Consider we have an existing table student, without any constraints applied to it. Later, we decided to apply a UNIQUE constraint to one of the table's column. Then we will execute the following query:


Constraints in SQL

To verify that the unique constraint is applied to the table's column and the student table is created successfully, we will execute the following query:


Constraints in SQL

3. PRIMARY KEY

  • PRIMARY KEY Constraint is a combination of NOT NULL and Unique constraints.
  • NOT NULL constraint and a UNIQUE constraint together forms a PRIMARY constraint.
  • The column to which we have applied the primary constraint will always contain a unique value and will not allow null values.

Syntax of primary key constraint during table creation:

Example:

Create a student table and apply the PRIMARY KEY constraint while creating a table.


Constraints in SQL

To verify that the primary key constraint is applied to the table's column and the student table is created successfully, we will execute the following query:


Constraints in SQL

Syntax to apply the primary key constraint on an existing table's column:

Example:

Consider we have an existing table student, without any constraints applied to it. Later, we decided to apply the PRIMARY KEY constraint to the table's column. Then we will execute the following query:


Constraints in SQL

To verify that the primary key constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

4. FOREIGN KEY

  • A foreign key is used for referential integrity.
  • When we have two tables, and one table takes reference from another table, i.e., the same column is present in both the tables and that column acts as a primary key in one table. That particular column will act as a foreign key in another table.

Syntax to apply a foreign key constraint during table creation:

Example:

Create an employee table and apply the FOREIGN KEY constraint while creating a table.

To create a foreign key on any table, first, we need to create a primary key on a table.


Constraints in SQL

To verify that the primary key constraint is applied to the employee table's column, we will execute the following query:


Constraints in SQL

Now, we will write a query to apply a foreign key on the department table referring to the primary key of the employee table, i.e., Emp_ID.


Constraints in SQL

To verify that the foreign key constraint is applied to the department table's column, we will execute the following query:


Constraints in SQL

Syntax to apply the foreign key constraint with constraint name:

Example:

Create an employee table and apply the FOREIGN KEY constraint with a constraint name while creating a table.

To create a foreign key on any table, first, we need to create a primary key on a table.


Constraints in SQL

To verify that the primary key constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

Now, we will write a query to apply a foreign key with a constraint name on the department table referring to the primary key of the employee table, i.e., Emp_ID.


Constraints in SQL

To verify that the foreign key constraint is applied to the department table's column, we will execute the following query:


Constraints in SQL

Syntax to apply the foreign key constraint on an existing table's column:

Example:

Consider we have an existing table employee and department. Later, we decided to apply a FOREIGN KEY constraint to the department table's column. Then we will execute the following query:


Constraints in SQL

Constraints in SQL

To verify that the foreign key constraint is applied to the department table's column, we will execute the following query:


Constraints in SQL

5. CHECK

  • Whenever a check constraint is applied to the table's column, and the user wants to insert the value in it, then the value will first be checked for certain conditions before inserting the value into that column.
  • For example: if we have an age column in a table, then the user will insert any value of his choice. The user will also enter even a negative value or any other invalid value. But, if the user has applied check constraint on the age column with the condition age greater than 18. Then in such cases, even if a user tries to insert an invalid value such as zero or any other value less than 18, then the age column will not accept that value and will not allow the user to insert it due to the application of check constraint on the age column.

Syntax to apply check constraint on a single column:

Example:

Create a student table and apply CHECK constraint to check for the age less than or equal to 15 while creating a table.


Constraints in SQL

To verify that the check constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

Syntax to apply check constraint on multiple columns:

Example:

Create a student table and apply CHECK constraint to check for the age less than or equal to 15 and a percentage greater than 85 while creating a table.


Constraints in SQL

To verify that the check constraint is applied to the age and percentage column, we will execute the following query:


Constraints in SQL

Syntax to apply check constraint on an existing table's column:

Example:

Consider we have an existing table student. Later, we decided to apply the CHECK constraint on the student table's column. Then we will execute the following query:


Constraints in SQL

To verify that the check constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

6. DEFAULT

Whenever a default constraint is applied to the table's column, and the user has not specified the value to be inserted in it, then the default value which was specified while applying the default constraint will be inserted into that particular column.

Syntax to apply default constraint during table creation:

Example:

Create a student table and apply the default constraint while creating a table.


Constraints in SQL

To verify that the default constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

Syntax to apply default constraint on an existing table's column:

Example:

Consider we have an existing table student. Later, we decided to apply the DEFAULT constraint on the student table's column. Then we will execute the following query:


Constraints in SQL

To verify that the default constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

7. CREATE INDEX

CREATE INDEX constraint is used to create an index on the table. Indexes are not visible to the user, but they help the user to speed up the searching speed or retrieval of data from the database.

Syntax to create an index on single column:

Example:

Create an index on the student table and apply the default constraint while creating a table.


Constraints in SQL

To verify that the create index constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

Syntax to create an index on multiple columns:

Example:


Constraints in SQL

To verify that the create index constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL

Syntax to create an index on an existing table:

Consider we have an existing table student. Later, we decided to apply the DEFAULT constraint on the student table's column. Then we will execute the following query:


Constraints in SQL

To verify that the create index constraint is applied to the student table's column, we will execute the following query:


Constraints in SQL