Constraints in SQL

Introduction

SQL constraints are a collection of rules used on tables in relational databases to limit the types of data that may be added, changed, or removed from those tables. By doing this, the information recorded in the table is guaranteed to be reliable and accurate. After the constraint is set up, any database operation that doesn't adhere to the restrictions set forth by the constraint is terminated. We will discuss SQL constraints in this post, including what they are, the many types that are frequently used, how to apply them, and how to remove them. But first, let's quickly go through the main benefits of constraints.

Why Do We Apply SQL Constraints to Tables?

The following is a list of the main reasons why SQL Constraints help us while creating and managing databases:

• Ensure that bad data is not added to important tables.
• Enforce business logic at the database level.
• Maintain relationship integrity between any number of tables.
• Boost the overall performance of the database.
• Important database rules are documented.
• Maintain uniqueness

NOT NULL Constraint

A column may by default contain NULL values. Therefore, a NOT NULL constraint is utilized in order to solve this problem when necessary. This constraint states that there can never be a blank cell value for any row in this column. Typically, this condition is applied to columns that store data that is extremely necessary to locate and retrieve from a table. The NOT NULL constraint can be set up either when the table is first created or at a later time using a MODIFY statement. Below, we can see the syntax for declaring a NOT NULL Constraint on the Customer_ID column during the creation of the table, named Customer:

1CREATE TABLE Customer (
2    Customer_Id int NOT NULL
3);

The int keyword stands for integer – which means that the fields of this column only accept integer values. In case we had created the table without specifying the NOT NULL constraint and would like to add it at a later time, we can do this by using the ALTER and MODIFY keywords, like so:

1ALTER TABLE Customer
2MODIFY Customer_Id int NOT NULL;

UNIQUE Constraint

The UNIQUE constraint states that no cell value in a column may be used more than once across the whole table. To put it another way, there can be no duplicate rows in this column of the table. A guarantee for uniqueness for a column or set of columns is provided by the UNIQUE and PRIMARY KEY constraints. Let us now create the same table as above, only this time let’s add another condition that the Customer_Id has to be unique, apart from not being null:

1CREATE TABLE Customer (
2    Customer_Id int NOT NULL UNIQUE
3);

If we’d like to ALTER an already created table, we’d use the ADD keyword this time instead of the MODIFY one. Let’s take a look:

1ALTER TABLE Customer
2ADD Unique(Customer_Id);

CHECK Constraint

All the records in a particular column must adhere to a specified criterion, and this is where the CHECK constraint comes in. This constraint is typically used to enforce business logic on values in a column, preventing the entry of inaccurate data. The CHECK constraint may be specified either when the table is created or may be added later using an ALTER statement. Imagine we add a column in our Costumer table which holds information about the customers’ age. Moreover, we’d like to make sure that all customers are over 18 years old. Then, we’d write the following query:

1CREATE TABLE Customer (
2    Customer_Id int NOT NULL UNIQUE,
3    Age int,
4    CHECK (Age>18)
5);

To create a CHECK constraint on the Age column after the table has been created, we use the following syntax:

1ALTER TABLE Customer (
2ADD CHECK (Age>18)
3);

DEFAULT Constraint

When a record's designated column is left empty, the DEFAULT constraint is used to establish a default value that must be entered. If no alternative value is provided, the default value will be appended to all new records. The DEFAULT constraint can be set either when the table is created or at a later time via an ALTER statement. In the following example, we create a column City and whenever a city regarding a specific customer is not specified, we add the default value, which in our case would be Unknown City.

1CREATE TABLE Customer (
2    Customer_Id int NOT NULL UNIQUE,
3    Age int,
4    City varchar(255) DEFAULT ‘Unknown City’,
5    CHECK (Age>18)
6);

In case the table is already created, use the following query to create a DEFAULT constraint on the City column:

1ALTER TABLE Customer (
2ALTER City SET DEFAULT ‘Unknown City’;
3);

PRIMARY KEY Constraint

PRIMARY KEYS serve as distinct identifiers for each row in a table. They can be values found in a table's single column or a combination of the table's several columns. The PRIMARY KEY column needs to be UNIQUE and cannot be NULL. The primary key in the table's value is a unique identifier for a specific row in the parent table that links the row to additional information present in the child table, where the same distinct identity also exists as a FOREIGN KEY. The PRIMARY KEY constraint can be set either when the table is created or at a later time using an alter statement. The following query creates a PRIMARY KEY on the Customer_Id column in the Customer table.

1CREATE TABLE Customer (
2    Customer_Id int NOT NULL UNIQUE,
3    Age int,
4    City varchar(255) DEFAULT ‘Unknown City’,
5    CHECK (Age>18)
6);

If we’d like to add a PRIMARY KEY after the creation of the table, we’d have to run the following ALTER statement:

1ALTER TABLE Customer (
2ADD PRIMARY KEY (Customer_Id);
3);

FOREIGN KEY Constraint

A foreign key is a field in a table that uniquely identifies each row of another table. A column (or set of columns) in one table that is the FOREIGN KEY relates to the PRIMARY KEY of another table. The table with the FOREIGN KEY is referred to as the child table, and the table with the PRIMARY KEY is referred to as the parent table. The FOREIGN KEY constraint may be specified either at the time the table is created or may be added later via an alter statement. Imagine we have a Customer table created with the constraints explained in the examples above, and another table called Orders, which keeps track of every order that has been made. These tables can be seen below:

1CREATE TABLE Orders (
2    Order_Id int NOT NULL UNIQUE,
3    PRIMARY KEY (Order_Id),
4    FOREIGN KEY (Customer_Id) REFERENCES Customer(Customer_Id)
5);

In order to create a FOREIGN KEY in an already created table, use the following syntax:

1ALTER TABLE Customer (
2ADD FOREIGN KEY (Customer_Id) REFERENCES Customer(Customer_Id);
3);

One Pager Cheat Sheet

• SQL constraints provide an important way to maintain the reliability and accuracy of data in relational databases, by limiting the types of data that may be added, changed, or removed from tables.
• Applying SQL Constraints to tables helps us to enforce business logic, maintain relationship integrity, prevent bad data, improve performance, and ensure uniqueness.
• SQL Constraints are used to guarantee quality data, restrict allowed data, enforce business logic, maintain relationships, ensure uniqueness, and improve performance, helping to guarantee database rules are followed.
• A NOT NULL Constraint is utilized to ensure that a column that stores important data will never contain a blank value.
• The UNIQUE constraint ensures that the values in the column it is specified for are unique and cannot be repeated.
• A CHECK constraint can be used to enforce business rules and restrict data entry, by specifying that all records in a database column must adhere to a specified criterion.
• The DEFAULT constraint establishes a default value that will be used if no alternative value is provided for a record's designated column.
• A PRIMARY KEY is a unique identifier for a table's row which cannot be NULL, and it can be set either during the creation of the table or afterward with an ALTER statement.
• A FOREIGN KEY is a field in a table that uniquely identifies each row of another table, which is specified either when the table is created with a CREATE TABLE query or with an ALTER TABLE query.
• A primary key ensures data consistency and accuracy while establishing relationships between tables, so having more than one would be illogical.