Constraints in SQL - The Comprehensive Guide

What is Constraint in SQL?

Constraints are rules that are applied to the data columns of a table. They are used to ensure the accuracy and consistency of the data in a table. If a constraint is violated, the action that is trying to be performed is aborted.

SQL constraints can be specified when the table is created or after the table is created. They can be column-level or table-level. Column-level constraints are applied to only one column, while table-level constraints are applied to the whole table.

Here are some examples of SQL constraints:

• NOT NULL constraint: This constraint ensures that the column cannot have a NULL value.
• UNIQUE constraint: This constraint ensures that each row in the table has a unique value in the specified column.
• CHECK constraint: This constraint ensures that the value in the column meets certain criteria. For example, you could use a CHECK constraint to ensure that the value in a column is between 1 and 100.
• FOREIGN KEY constraint: This constraint ensures that the value in one column refers to a valid value in another column. For example, you could use a FOREIGN KEY constraint to ensure that the customer_id column in the orders table refers to a valid value in the customers table.
• Constraints are an important part of data integrity. By using constraints, you can ensure that the data in your tables is accurate and consistent. This can help to prevent errors and improve the quality of your data.

If you are interested in learning more about SQL constraints, I recommend checking out Intellipaat’s SQL Training Course. This course covers all of the essential topics of SQL, including constraints.

Constraints in SQL

Some of the commonly used Constraints in SQL are:

• Not NULL Constraints in SQL
• UNIQUE Constraints in SQL
• PRIMARY KEY Constraints in SQL
• FOREIGN KEY Constraints in SQL
• CHECK Constraints in SQL
• DEFAULT Constraints in SQL

• Not NULL:

It indicates that the column cannot have null value.

• UNIQUE:

It ensures that all the values in the column are unique.

• PRIMARY KEY:

It is a key which helps you to find the data from the table. It must be unique and not null.

• FOREGIN KEY

A foreign key is a field in a relational table that matches the primary key column of another table.

• CHECK

It checks whether all values meets the specific condition or not.

• DEFAULT

It provides the default value when the value is not specified.
Syntax:

CREATE TABLE table_name
(
Column1 datatype (size) constraint_name,
Column2 datatype (size) constraint_name,
Column3 datatype (size) constraint_name,
……….
);