SQL Constraint Types, Syntax and Examples

We have large collections of data in our databases, and to maintain the correctness of the data, we require logical checks on the data. In SQL, we have constraints as the logical checks for the data.

Constraints are important to avoid ambiguity in data. We can put constraints on the whole table or only the columns depending on how we want to manage our data.

They are the rules made to keep up the integrity and correctness of data. When we use constraints, we put a check on our data, which helps us to follow a specified pattern.

For example: When we deal with the data of a bank account, we need to have a constraint that would help us to uniquely identify all the customers associated with the bank.

Thus, we require constraints to meet all these industrial requirements. For the use case of a bank, we will be using the UNIQUE constraint to keep the identification number or the account number different to each user of the banking system.

What are the Constraints and Use of Constraints?

Constraints are rules which we apply to the whole table or the columns to restrict what and how the data is put in the database.

We use constraints to maintain the effectiveness and integrity of our data. By using constraints, we can keep our data free from ambiguity and unwanted errors. Constraints are of two types:

1. Table Level Constraints: These constraints are applicable to the whole table.
2. Column Level Constraints: These constraints are applicable to the data stored in the column.

Syntax:

CREATE TABLE tableName (
  columnName1 datatype constraint1,
  columnName2 datatype constraint2,
  columnName3 datatype constraint3,
  ....
);

Types of Data Constraints

1. Input/Output Constraints: We use input and output constraints to check the speed of insertion, extraction, and deletion. Example: Primary Key, Foreign Key, etc.

2. Business Constraints: These are applicable on the data before the data is tucked in the table. Example: Unique, Null, etc.

Different SQL Constraints and Usage

Sr.No	Constraint in SQL	Usage
1	NOT NULL	Used when the column should not take null values.
2	UNIQUE	Used when all the values in the column need to be different.
3	PRIMARY KEY	Used so that each row in a table has a unique identity.
4	FOREIGN KEY	Used to identify rows or records stored in another table.
5	CHECK	Used to ensure that all the data in the column satisfies some given condition.
6	DEFAULT	Used to set the default value for the column when no value is put by the user.
7	INDEX	Used to retrieve and search data in a database quickly.

Examples of SQL Constraint

1. Use of NOT NULL and UNIQUE Constraint in SQL

Query:

CREATE TABLE DataFlair_Emp
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
email varchar(20)
);

2. Using PRIMARY and FOREIGN Key Constraint in SQL

Query:

CREATE TABLE DataFlair_1
(
ID int NOT NULL,
ORDER_NO int,
C_ID int,
PRIMARY KEY (ID),
FOREIGN KEY (ID) REFERENCES DataFlair_2(ID)
)

3. Usage of CHECK and DEFAULT Constraint in SQL

Query:

CREATE TABLE DataFlair_3
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18),
Company varchar(20) DEFAULT 'DataFlair'
);

Summary

SQL constraints are beneficial to store data in an orderly fashion in the database. We have been discussing the SQL constraints and examples.

Constraints are important to maintain the integrity and effectiveness of the data we have in our database over time. Using SQL, we store data in such a fashion that it is free from ambiguities and unwanted errors.

Constraints like the Primary key and Foreign key make it easy for us to retrieve data and check-in multiple tables for the same. The check constraint helps us to keep the columns free from null values.

When null values are put in a database unwanted errors creep in, causing logical and mathematical errors.

By proper usage of constraints, we can keep our data error-free and ensure that all the logical and mathematical operations are yielding proper results.