Databases are an important tool because they store the data that powers our daily lives. Databases are designed to match the real world as much as possible, so they store different forms of data about different things, as it is in the world.
There are many rules that govern how entities communicate with each other, in order to make things work. For example, a student cannot take a course that the school does not offer. A football player cannot have less than 1 or less than 99 jersey numbers. And a car must always have a plate number.
Relational databases are also able to represent and enforce these rules Obstacles. And in this article, I will explain how constraints work with practical examples.
Whether you’re a beginner or just looking to brush up on your knowledge, this article will help you learn the essentials. If you need some more background, you can read this article on relational database basics before continuing.
What we will cover:
What is a relational database constraint?
Relational database constraints are a set of database rules used to define or determine which set of values ​​are acceptable or valid in a database. They are usually based on many real-world principles.
They are placed in place:
Ensure data validity: Only values ​​that would be acceptable in real life should be acceptable in the database. Learn more about data accuracy Here.
Ensure data integrity: Values ​​in the database remain true, correct, complete and correct as long as the database exists. Learn more about data integrity Here.
Ensure data consistency: Values ​​always maintain the same consistent format throughout their lifetime.
These rules limit what can be inserted into or deleted from the database. They also restrict data updates to ensure accuracy after original creation.
These integrity constraints help enforce business rules on data in tables to ensure data integrity and reliability. – from AWS
Types of relational database constraints
There are many ways to group or classify database constraints, depending on how they are applied or what they are preventing. This article focuses on three popular types:
Inherent Model-Based Constraints (Implicit Constraints)
Schema-based Constraints (Explicit Constraints)
Application-Based Constraints (Sense Constraints)
Inherent Model-Based Constraints (Implicit Constraints)
These rules are the basic rules that come with the database and are enforced by DMBS. Some of these rules are:
Each row must be unique. It is with or without one
UNIQUEorPRIMARY KEYObstacleColumns can store only one value at a time. such as field value
ageThere will always be a value like 23 and 35.Each column name in a table must be unique.
Columns exist for all rows. Each row will have the same number of columns. For some rows, the data may be empty, but the column will always be there.
Schema-based Constraints (Explicit Constraints)
These constraints are expressed by the developer or database designer at database creation. They are expressed directly in database schemas using them DDL.
They can be further broken down into:
Domain Constraints
Key Constraints
Entity integrity constraint (primary key)
unique constraint (unique key)
referential integrity constraint (foreign key)
1. Domain constraints
These are used to define a range or set of possible values ​​for a database table attribute. They help ensure that column values ​​are valid and consistent by defining acceptable data types, formats, and the bounds of an attribute. It prevents incorrect or illogical data entry and maintains data integrity.
You can define them simply by specifying a data type whose values ​​must behave. For example, age There can be only one number from a person, or a number between 5-65 if the database is for a company, or between 18-60 if it is for an amusement park.
The database will enforce this rule by rejecting age values ​​outside the given range or type. The DDL for the age will look like this:
CREATE TABLE people (
age INT,
age INT CHECK (age BETWEEN 18 AND 60),
age INT CHECK (age BETWEEN 5 AND 65)
);
INT This means that only numeric values ​​are accepted, and CHECK Used with BETWEEN And AND Keywords to define a subdomain or range of values.
Other Data Types in SQL Includes: CHARfor , for , for , . BITfor , for , for , . DATEfor , for , for , . VARCHAR And so you can use all of them to define acceptable domains for database values.
CREATE TABLE employees (
employee_id INT,
name VARCHAR(100),
age INT CHECK (age BETWEEN 18 AND 60)
);
In addition to specifying a range of acceptable values, you can also specify an attribute option using NOT NULL Keyword You would use this in cases where the data exists and must also be in a given range.
CREATE TABLE employees (
employee_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
age INT CHECK (age BETWEEN 18 AND 60)
);
In this example, each employee record needs one employee_id And a name But not one age. This works for real-life situations where, although the range of values ​​is known, the actual value is either unknown or not. An example of this would be a student’s normal course of study at university – many students only have majors, and so, the normal course of study would be empty (null) for those students.
2. Entity integrity constraint (primary key)
This ensures that no primary key is null. A primary key is an attribute or set of attributes that must be unique for each row in the database. It is the base value that uniquely identifies the rest of the data. This means that each row in the database will be uniquely identifiable with a primary key.
A null primary key means that rows will not be unique, or identifiable, and there may be duplicates in the database. Without a primary key, we cannot have data consistency.
For example, in a school, each student will have a unique student identification number with which they can always be distinguished from other students. Governments use methods such as passport numbers or tax IDs to uniquely identify citizens.
In our example, it is impossible to become a student without a student ID number. You can implement this constraint using PRIMARY KEY Keyword
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
age INT CHECK (age BETWEEN 18 AND 60)
);
3. Unique Constraint (Unique Key)
It’s like Entity integrity constraint In that it only accepts unique values ​​- but it differs in that it accepts null values.
An example of this would be in a student table, each student must have a student ID number that uniquely identifies them. This number cannot be null, and must be unique. Students may also have an email address at which the school can reach them. This email should be unique for each student. But, not every student should have an email. So the situation is: “If there is value, it must be unique.”.
You can implement this constraint using UNIQUE Keywords, like this:
CREATE TABLE students (
student_id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE
);
4. Referential integrity constraint (foreign key)
This constraint protects the relationship between two related tables. It is used to maintain consistency in relationships. This requires that data from one table, A, referenced in another table, B, must exist in the original table, A. For example, a student cannot register for a course that the school does not have.
To implement this, FOREIGN KEY Used with the keyword REFERENCES To specify the table to be referenced, and which attribute is being referenced.
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
course_id INT,
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
In this example, provided in each value course_id Of students I should be courses Table
Application-Based Constraints (Sense Constraints)
They can also be called Business Rules. They cannot be expressed directly in the database schema, so they are often implemented at the application layer instead.
These are logical constraints, so to speak “A course cannot admit more than 30 students” or “A customer cannot place an order if their credit limit is exceeded”.
These rules are best implemented in the application, as it would be too complex (or sometimes impossible) to implement them on the database itself.
Test constraints
To demonstrate the constraints we’ve discussed here, let’s look at this sample school database setup:
CREATE TABLE courses (course_id INT PRIMARY KEY, course_name VARCHAR(100) NOT NULL, max_students INT CHECK (max_students > 0));
CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, age INT CHECK (age BETWEEN 5 AND 25));
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE NOT NULL,
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (course_id) REFERENCES courses (course_id)
);
This shows the creation of a sample school database with three tables: coursesfor , for , for , . studentsand enrollments.
courses The table contains a primary key for course IDs, course names, and a constraint that ensures that the maximum number of students is greater than zero. students The table contains a primary key for student IDs, student names, unique email addresses, and an age constraint between 5 and 25. enrollments Table associates students to courses with primary keys for enrollment IDs and foreign keys students And courses Tables, with non-null entry dates.
At this point, the tables are made, and set up with obstacles guiding them.
Now we will examine some questions:
- Enter course, math and history
coursestable:
INSERT INTO courses (course_id, course_name, max_students) VALUES (1, 'Mathematics', 30);
INSERT INTO
courses (course_id, course_name, max_students)
VALUES
(2, 'History', 25);
The query works perfectly, as soon as the record is inserted.
- Enter the students, Alice and Bob
studentstable:
INSERT INTO
students (student_id, student_name, email, age)
VALUES
(101, 'Alice', 'alice@example.com', 20);
INSERT INTO
students (student_id, student_name, email, age)
VALUES
(102, 'Bob', NULL, 18);
The query works perfectly, as soon as the record is inserted.
- Enter Alice in Mathematics:
INSERT INTO
enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
(1001, 101, 1, '2026-01-14');
The query works perfectly, because the record gets inserted.
- Enter a new student, Charlie
studentstable:
INSERT INTO
students (student_id, student_name, email, age)
VALUES
(103, 'Charlie', 'charlie@example.com', 30);
It fails because Charlie has one age 30 value, which is outside the specified range age INT CHECK (age BETWEEN 5 AND 25). Charlie’s record is never included.
Here is a list of some other queries that will fail:
INSERT INTO
students (student_id, student_name, email, age)
VALUES
(104, 'David', 'alice@example.com', 19);
INSERT INTO
students (student_id, student_name, email, age)
VALUES
(NULL, 'Evra', 'evra@example.com', 20);
INSERT INTO
enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
(1002, 999, 1, '2026-01-14');
In each case, the DBMS will provide a reason for rejection or failure.
- Delete the chapter
studentstable:
DELETE FROM students
WHERE
student_id = 102;
The query works perfectly, because the record is deleted.
- Delete Alice
studentstable:
DELETE FROM students
WHERE
student_id = 101;
It fails because with Alice student_id Out of 101, I have a record enrollment enrollments Deleting a table record would mean that there would be an enrollment record for a non-existent student which should not be possible.
How to Delete a Record
In some cases, you want to delete a record, even though there are records associated with it. There are two main ways to go about this:
The waterfall
You can use it to describe situations where, when the parent record is deleted, the child record may no longer exist. Other tables contain all dependent (child) records Automatically deleted. You can use this to ensure that all enrollment records are deleted when a course is no longer available, or when a student is no longer in school.
CREATE TABLE enrollments (enrollment_id INT PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, FOREIGN KEY (course_id) REFERENCES courses (course_id) ON DELETE CASCADE);
DELETE FROM courses
WHERE
course_id = 1;
Undo or set default
You can use these methods to define situations where child records without parents may still exist. Other tables contain all dependent (child) records Automatically set to NULL or Automatically set to a defined default.
A useful example of this is if students at a school have a mentor assigned to them, when the mentor leaves the school, you don’t want to delete the students – you want to set the mentor to null or the default staff.
CREATE TABLE teachers (teacher_id INT PRIMARY KEY, teacher_name VARCHAR(100) NOT NULL);
CREATE TABLE students (student_id INT PRIMARY KEY, student_name VARCHAR(100) NOT NULL, mentor_id INT, FOREIGN KEY (mentor_id) REFERENCES teachers (teacher_id) ON DELETE SET NULL);
- Update Alice’s details. Change his email to a new one, and add his age:
UPDATE students
SET
email = 'alice.new@example.com',
age = 22
WHERE
student_id = 101;
The query works perfectly, because the record gets updated.
- Update Alice’s age to 30:
UPDATE students
SET
age = 30
WHERE
student_id = 101;
It fails for the same reason as the fourth test: age is outside the specified range.
Here’s another query that will fail:
UPDATE enrollments
SET
course_id = 999
WHERE
enrollment_id = 1001;
It will fail because new course_id I don’t exist courses Table
Summary
Databases are an important part of everyday modern technology, and understanding their basic concepts can open the door to more accurate database construction and management.
This article introduced you to what relational database constraints are, some of the different types, and how they are enforced and violated. You should now have the knowledge necessary to confidently navigate the world of database constraints.
If you are interested to know more, contact me LinkedInfor , for , for , . Twitteror GitHub. Let’s continue this journey to mastering database systems together!