Aviator

Posted on May 18, 2023

SQL Constraints 101: Ensuring Data Accuracy and Integrity

#sql #database #data #datascience

Enforcing restrictions on the type of data that goes into a database table is a way to protect it from malicious attacks and have consistent data that adheres to requirements. When creating a database table, you may want to put restrictions on the type of data that gets stored in the table.
For example, when creating a table for students for a particular institution, each student is assigned a unique id, for which no other student is assigned that id. If some other student is being assigned the id that is already assigned, then that id cannot be assigned. This simply means that no two students can have the same student id.

The purpose of imposing constraints is to maintain the integrity and accuracy of the data stored.
Any attempt to store values that violate what the table expects, the database will throw an error. They are simply rules that are used to restrict the type of data that can go into either a table or column(s).

Benefits of SQL constraints:

Maintaining data integrity SQL constraints ensure that the data stored in a database is accurate and consistent by enforcing rules that prevent invalid or inconsistent data from being entered.
Avoiding errors By preventing invalid or inconsistent data from being entered, SQL constraints help avoid errors that can arise from incorrect data.
Improving performance By enforcing data rules at the database level, SQL constraints can help improve the performance of database queries and reduce the risk of application crashes.
Enforcing business rules SQL constraints can be used to enforce business rules and ensure that data entered into a database meets the requirements of the organization, leading to more effective decision-making and data analysis.

Constraints are categorized into two

Table Level Constraints: Constraints defined independent of the column definition. These types of constraints are usually defined at the end of the create table statement.

CREATE TABLE Student (
  student_id INT SERIAL,
  department_name VARCHAR(100),
  college_name VARCHAR(100),

  CONSTRAINT student_pk PRIMARY KEY(student_id)
);

Column Level Constraints: Constraints defined during column definition. They are usually written at the column level where we want the constraints to be enforced.

CREATE TABLE Students (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(20) NOT NULL,
  college_name VARCHAR(50) NOT NULL
);

SQL constraints can be defined when the table is first created, they can also be added using the ALTER command.

The different constraints used in SQL include:

Primary Key Constraint
Not Null Constraint
Unique Constraint
Check Constraint
Default Constraint
Foreign Key Constraint

PRIMARY KEY Constraint

The PRIMARY KEY constraint is used to uniquely identify rows/records created in a table. One or more combination of columns in a table defines the primary key.
Primary keys are enforced to contain unique values and they cannot be with null values.

CREATE TABLE Students (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(100),
  college_name VARCHAR(200)
);

Here the primary key constraint is defined for the student_id and it is defined at the column level.

Using the ALTER command

CREATE TABLE Student (
  student_id INT SERIAL,
  department_name VARCHAR(100),
  college_name VARCHAR(100)
);

ALTER TABLE Student ADD PRIMARY KEY (student_id);

Table Level Primary Key Constraints

CREATE TABLE Student (
  student_id INT SERIAL,
  department_name VARCHAR(100),
  college_name VARCHAR(100),

  CONSTRAINT Student_PK PRIMARY KEY (student_id)
);

NOT NULL Constraint

The NOT NULL constraint specifies that the values to be inserted in a particular column cannot be blank.

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100) NOT NULL
);

Here, the department_name and the college_name columns of the Students table won't accept NULL values.

Using the ALTER command

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100)
);

ALTER TABLE Student ALTER COLUMN college_name SET NOT NULL;

Error when inserting NULL values
A value must be inserted into columns with the NOT NULL constraint. Otherwise, SQL will throw an error.
In the example below, SQL will throw an error because the value of college_name is not given.

INSERT INTO Students (student_id, department_name, college_name) 
VALUES (1, 'Economics')

UNIQUE Constraint

The UNIQUE constraint means that the column must have a unique value. Duplicate values are not allowed in columns to which the UNIQUE constraint is applied.
Unique constraints allow for null values. That is, null values can be stored in columns with a UNIQUE constraint.

CREATE TABLE Student (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100) NOT NULL,
  student_email varchar(100) UNIQUE,
);

Table-level UNIQUE constraints

CREATE TABLE Students (
student_id INT PRIMARY KEY,
department_name VARCHAR(20) NOT NULL,
college_name VARCHAR(50) NOT NULL,
student_email varchar(50),
student_phone INT,

CONSTRAINT unique_column UNIQUE (student_email)
);

Using ALTER command

CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  department_name VARCHAR(20) NOT NULL,
  college_name VARCHAR(50) NOT NULL,
  student_email varchar(50),
  student_phone INT
);

ALTER TABLE Students ADD UNIQUE (student_email);

Above, the SQL command adds the UNIQUE constraint to the student_email columns in the existing Student table.

-- Add a unique constraint to multiple columns
ALTER TABLE Student ADD UNIQUE (student_email, student_phone);

Error when inserting duplicate values
We will get an error if we try to insert duplicate values in a column with the UNIQUE constraint.

CREATE TABLE Student (
  student_id INT SERIAL PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  college_name VARCHAR(100) NOT NULL,
  student_email VARCHAR(100) UNIQUE,
  student_phone INT UNIQUE
);

-- Insert values to the Student table
-- student_email has duplicate values

INSERT INTO Student(student_id, department_name,college_name, 
        student_email, student_phone)
VALUES (1, "Philosophy", "Madonna University", "mymy@gmail.com", 4487),
       (2, "Sociology", "Gallas University", "mymy@gmail.com", 9976 );

CHECK Constraint

The CHECK constraint is used to ensure that all the records in a certain column follow a specific rule.
When the user wants to insert the value into a column with the CHECK constraint applied, the value will first be checked to make sure it meets the defined conditions before inserting the value into that column.

Example: If the table has a salary column defined, without the check constraint, the user may insert any value of their choice, which may be below or above the required range.
But, if the user has applied a check constraint on the salary column, the column will not accept a value that isn't defined in its requirements.
This prevents salaries from being entered below or above the known salary range.

CREATE TABLE employees (
  employee_id INT NOT NULL PRIMARY KEY,
  employee_name VARCHAR(55) NOT NULL,
  salary INT NOT NULL CHECK (salary >= 1000 AND salary <= 10000)
);

Table-level CHECK constraint

CREATE TABLE employees (
  employee_id INT NOT NULL PRIMARY KEY,
  employee_name VARCHAR(55) NOT NULL,
  salary INT NOT NULL

  CONSTRAINT salaryCK CHECK (salary >= 1000 AND salary <= 10000)
);

Using the ALTER Command

CREATE TABLE employees (
employee_id INT NOT NULL PRIMARY KEY,
employee_name VARCHAR(55) NOT NULL,
salary INT NOT NULL
);

ALTER TABLE employees 
  ADD CONSTRAINT salaryCK CHECK (salary >= 1000 AND salary <= 10000)

DEFAULT Constraint

The DEFAULT constraint is used to set the default value for a column when the value is NULL.

CREATE TABLE User (
  id NOT NULL SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50),
  country VARCHAR(30) NOT NULL DEFAULT 'Belgium'
);

Using the ALTER command

CREATE TABLE User (
  id NOT NULL PRIMARY KEY SERIAL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50),
  country VARCHAR(30) NOT NULL
);

ALTER TABLE User
  ALTER COLUMN country SET DEFAULT 'Belgium';

Inserting values into the table

-- Using the insert command, the default value of the country column 
-- is set to Belgium.

-- So, when we try to insert a NULL value to the country column, 
-- it is replaced with Belgium by default.

INSERT INTO User (id, first_name, last_name, country) 
  VALUES (1, 'John', 'Agba');

INSERT INTO User (id, first_name, last_name, country) 
  VALUES (1, 'John', 'Agba', 'Nigeria);

However, when we set the value of the country column, the default value is ignored and the value of the column is set.

FOREIGN KEY Constraint

The FOREIGN KEY constraint is a column used to reference a record that exists in another table.
Simply, it is a column in a table that refers to the primary key in another table. The table with the foreign key is called the child table while the referenced table with the primary key is called the parent table.

Example

CREATE TABLE Customer (
  customer_id INT PRIMARY KEY,
  address VARCHAR(200) NOT NULL,
  email varchar(100) UNIQUE
);

CREATE TABLE Orders (
  id INT PRIMARY KEY,
  product VARCHAR(50),
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);

Using the ALTER Command

CREATE TABLE Customer (
  id INT PRIMARY KEY,
  address VARCHAR(200) NOT NULL,
  email varchar(100) unique
)

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  product varchar(50),
  customer_id
)

ALTER TABLE Orders
  ADD FOREIGN KEY (customer_id) REFERENCES Customer(id);

Conclusion

In conclusion, SQL constraints are an essential aspect of database design, enabling developers and administrators to maintain data integrity, and ensuring that the data stored in your database is accurate, consistent, and secure.
SQL constraints also help to reduce errors and improve database performance, by preventing invalid data from being inserted.
Throughout this article, we have explored the different types of SQL constraints, such as primary keys constraints, foreign keys constraints, unique constraints, and check constraints, and provided examples of how they can be used in practice.

Implementing SQL constraints on a database table is a best practice that can help to build a robust and reliable system.
I hope that this article has provided you with a clear understanding of SQL constraints and how they can be used to build better, more secure, and more efficient databases.

Connect with me on Twitter or Linkedin