SQL

Constraints in SQL

Constraints in SQL

Introduction

We have huge data collections in our databases, and we need logical checks on the data to ensure the data's accuracy. Constraints serve as the logical tests for the data in SQL. In order to prevent data ambiguity, constraints are essential. Depending on how we want to handle our data, we may impose limitations on the entire table or only the columns. They are the regulations designed to maintain the accuracy and integrity of data. By placing restrictions on our data, constraints enable us to adhere to a preset pattern.

For instance, when working with bank account data, we want a constraint that enables us to identify each client connected to the bank in a unique way. To fulfill all of these industrial criteria, we thus need restrictions. To ensure that each user of the banking system has a unique identification number or account number for the use case of a bank, we will utilize the UNIQUE constraint.

What are the constraints and how are they used?

In order to limit what and how data is entered into the database, constraints are rules that we apply to the entire table or the columns. Constraints are used to preserve the accuracy and reliability of our data. Constraints allow us to prevent ambiguity and unintended mistakes in our data. There are two sorts of constraints:

  1. Table Level Constraints: The entire table is subject to these restrictions.
  2. Column Level Constraints: These limitations apply to the information kept in the column.

Syntax:

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

1. Input/Output Constraints: To test the insertion, extraction, and deletion times, we employ input and output restrictions. Primary keys, foreign keys, etc. are examples.

2. Business Constraints: These are relevant to data that has not yet been wrapped into a table. For instance, Unique, Null, etc.

Different SQL Constraints and Usage

Constraint in SQL

Usage

NOT NULL

When a column shouldn't accept null values, use this.

UNIQUE

Used when every value in the column must be unique.

PRIMARY KEY

Used to provide a distinct identity for each row in a table.

FOREIGN KEY

Used to locate rows or entries kept in a different table.

CHECK

Used to verify that every piece of data in the column complies with a certain requirement.

DEFAULT

Used to establish the column's default value when the user doesn't enter one.

INDEX

Used to quickly obtain and search data from a database.

Examples of SQL Constraint

1. Utilization of SQL's NOT NULL and UNIQUE Constraints

Query:

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

2. Utilizing the SQL PRIMARY and FOREIGN Key Constraint

Query:

CREATE TABLE BoardInfinity_1
(
ID int NOT NULL,
ORDER_NO int,
C_ID int,
PRIMARY KEY (ID),
FOREIGN KEY (ID) REFERENCES BoardInfinity_2(ID)
)

3. Application of SQL's CHECK and DEFAULT Constraints

Query:

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

Summary

SQL constraints are useful for organizing data storage in a database. The SQL restrictions and examples have been covered. In order to preserve the accuracy and usefulness of the data we currently have in our database throughout time, constraints are crucial. We save data using SQL in a way that prevents ambiguity and unintended mistakes. The primary key and foreign key constraints, for example, make it simple for us to obtain data and verify it into several tables for the same. The check constraint enables us to prevent null values from appearing in the columns.

Unwanted mistakes sneak into databases when null values are entered, resulting in logical and mathematical problems. Constraints may be used effectively to verify that our data is error-free and that all logical and mathematical processes provide accurate outputs.

Table of Content