There are many rules that govern how entities interact with each other, to make things work. For example, a student can’t take a course that the school doesn’t offer. A soccer player can’t have a jersey number less than 1 or greater than 99. And a car must always have a plate number.

Relational databases are also able to represent and enforce these rules using constraints. And in this article, I’ll explain how constraints work with practical examples.

Whether you’re a beginner or just looking to refresh your knowledge, this article will help you learn the essentials. If you need some more background, you can read this article on the basics of relational databases before continuing.

What We’ll Cover:

1. What is a Relational Database Constraint?

2. Types of Relational Database Constraints

   • Inherent Model-based Constraints (Implicit Constraints)

   • Schema-based Constraints (Explicit Constraints)

   • Application-based constraints (Semantic constraints)

3. Testing Constraints

   • How to Delete a Record

4. Summary

What is a Relational Database Constraint?

Relational database constraints are a set of database rules that are used to define or determine what set of values are acceptable or valid in a database. They’re usually based on the many rules of the real world.

They are put in place to:

• Ensure data accuracy: 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 correct, accurate, complete, and valid as long as the database exists. Learn more about data integrity here.

• Ensure data consistency: values always maintain same agreed form throughout their lifetime.

These rules limit what can be entered into a database or what can be deleted from it. They also limit data update to ensure validity after original creation.

These integrity constraints help enforce business rules on data in the tables to ensure the accuracy and reliability of the data. - AWS

Types of Relational Database Constraints

There are many ways to group or categorise database constraints, depending on how they’re applied or what they’re preventing. This article focuses on three popular types:

• Inherent model-based constraints (implicit constraints)

• Schema-based constraints (explicit constraints)

• Application-based constraints (semantic constraints)

Inherent Model-based Constraints (Implicit Constraints)

These rules are the base rules that come with the database and are enforced by the DMBS. Some of these rules are:

• Each row must be unique. This is with or without a UNIQUE or PRIMARY KEY constraint.

• Columns can only store one value at a time. The value of a field like age will always be one value like 23, not 23 and 35.

• Each column name in a table must be unique.

• Columns exist for all rows. Every row will have the same number of columns. For some of the rows, the data might be empty, but the column will always be there.

Schema-based Constraints (Explicit Constraints)

These constraints are expressed by the developer or database designer on database creation. They’re expressed directly in the database schemas, using the DDL.

These 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 an attribute of a database table. They help ensure that column values are valid and consistent by defining acceptable data types, formats, and ranges for an attribute. This prevents incorrect or illogical data entry and maintains data integrity.

You can define them simply by specifying a data type that the values must follow. For example, the age of a person can only be a number, or could be a number between 18-60 if the database is for a company, or a number between 5-65 if it’s for an amusement park.

The database will enforce this rule by rejecting age values outside of the given range or type. The DDL for the age would look like this:

CREATE TABLE people (
    age INT, -- Any integer value is allowed
    age INT CHECK (age BETWEEN 18 AND 60), -- Only allows ages between 18 and 60
    age INT CHECK (age BETWEEN 5 AND 65) -- Only allows ages between 5 and 65
);

The INT means only integer values are accepted, and the CHECK is used with the BETWEEN and AND keywords to specify the sub-domain or range of values.

Other data types in SQL include: CHAR, BIT, DATE, VARCHAR and so on. You can use all of them to define the acceptable domain for database values.

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    age INT CHECK (age BETWEEN 18 AND 60)
);

As well as defining a range of acceptable values, you can also define the optionality of an attribute using the NOT NULL keyword. You’d use this in cases where the data must exist and must also be within the 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, every employee record needs to have an employee_id and a name but not an age. This works for real life situations where, although the range of values is known, the actual value is either unknown or doesn’t exist. An example would be the minor course of study of a student at a university – many students only have majors, and as such, the minor course of study will be empty (NULL) for those students.

2. Entity integrity constraint (Primary key)

This ensures that no primary key is NULL. The primary key is the one attribute or set of attributes that must be unique to each row in the database. It’s the primary value that uniquely identifies the rest of the data. This means that every row in the database will remain uniquely identifiable with a primary key.

A NULL primary key means that rows will not be unique, or identifiable, and the database can contain duplicates. Without the primary key, we can’t have data consistency.

For example, in a school, every student will have a unique student id number with which they can always be distinguished from other students. The government uses methods like passport numbers or tax ids to uniquely identify citizens.

In our example, it’s impossible to be a student without a student id number. You can implement this constraint by using the 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)

This is similar to the Entity integrity constraint in that it only accepts unique values – but it’s different in that it accepts NULL values.

An example of this would be in a students table, every student must have a student id number that uniquely identifies them. This number cannot be NULL, and it must the unique. Students can also have an email address that the school can reach them on. This email must be unique for each student. But, not every student has to have an email. So the condition is: “If the value exists, it must be unique”.

You can implement this constraint using the UNIQUE keyword, like this:

CREATE TABLE students (
    student_id INT PRIMARY KEY, -- Must exist and must be unique
    email VARCHAR(255) UNIQUE -- Can be NULL, but must be unique if provided
);

4. Referential integrity constraint (Foreign key)

This constraint guards the relationship between two related tables. It is used to maintain consistency in the relationship. It requires that data from one table, A, being referenced in another table, B, must exist in the original table, A. For example, a student can’t register for a course the school doesn’t have.

To enforce this, the FOREIGN KEY keyword is used with the REFERENCES to define the table being referenced, and what attribute is being referred to.

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, every value provided in the course_id of the students must be in the courses table.

Application-based constraints (Semantic constraints)

These can also be called business rules. They can’t be directly expressed in the database schema, so they’re often implemented the application layer instead.

These are logical constraints, like saying “a course cannot have more than 30 students enrolled” or “a customer cannot place an order if it would exceed their credit limit”.

These rules are best implemented in the application, because it would be too complex (or sometimes impossible) to implement them on the database itself.

Testing 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: courses, students, and enrollments.

The courses table includes a primary key for course IDs, course names, and a constraint ensuring that the maximum number of students is greater than zero. The students table contains a primary key for student IDs, student names, unique email addresses, and an age constraint between 5 and 25. The enrollments table links students to courses with primary keys for enrollment IDs and foreign keys referencing the students and courses tables, along with a non-null enrollment date.

At this point, the tables are created, and setup with the constraints guiding them.

Now we’ll test a few queries:

1. Insert courses, Mathematics and History, into the courses table:

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 the records get inserted.

2. Insert students, Alice and Bob, into the students table:

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 the records get inserted.

3. Enroll Alice into Mathematics:

INSERT INTO
    enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
    (1001, 101, 1, '2026-01-14');

The query works perfectly, as the record gets inserted.

4. Insert a new student, Charlie, into the students table:

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (103, 'Charlie', 'charlie@example.com', 30);

This fails because Charlie has an age value of 30, which is outside of the specified range of age INT CHECK (age BETWEEN 5 AND 25). The record of Charlie never gets added.

Here’s 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); -- Fails for duplicate email

INSERT INTO
    students (student_id, student_name, email, age)
VALUES
    (NULL, 'Evra', 'evra@example.com', 20); -- Fails for NULL primary key

INSERT INTO
    enrollments (enrollment_id, student_id, course_id, enrollment_date)
VALUES
    (1002, 999, 1, '2026-01-14'); -- Fails for invalid student reference

In each case, the DBMS will provide a reason for the rejection or failure.

5. Delete Bob from the students table:

DELETE FROM students
WHERE
    student_id = 102;

The query works perfectly, as the record gets deleted.

6. Delete Alice from the students table:

DELETE FROM students
WHERE
    student_id = 101; -- Fails for referential integrity constraint

This fails because Alice, with student_id of 101, has an enrollment record in the enrollments table. Deleting the record would mean there will be an enrollment record for a non-existent student which should not be possible.

How to Delete a Record

In some cases, you do want to delete a record, even though it has records tied to it. There are two main ways to go about this:

CASCADE

You can use this to define situations where, when a parent record is deleted, the child records cannot exist. All dependent (child) records in other tables are automatically deleted. You can use this to ensure that all enrollment records are deleted when the course is no longer available, or when a student is no longer in the 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;

SET NULL or SET DEFAULT

You can use these methods to define situations where child records can still exist without the parent. All dependent (child) records in other tables are automatically set to null or automatically set to a defined default.

A useful example is if a school had a mentor assigned to students, when the mentor leaves the school, you don’t want to delete the students – you want to set the mentor to NULL or a 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);

7. Update Alice’s details. Change her email to a new one, and increase her age:

UPDATE students
SET
    email = 'alice.new@example.com',
    age = 22
WHERE
    student_id = 101;

The query works perfectly, as the record gets updated.

8. Update Alice’s age to 30:

UPDATE students
SET
    age = 30
WHERE
    student_id = 101;

This fails just like the 4th test for the same reason: the age is out of the stated range.

Here’s another query that will fail:

UPDATE enrollments
SET
    course_id = 999
WHERE
    enrollment_id = 1001;

This will fail because the new course_id does not exist in the courses table.

Summary

Databases are a pivotal part of everyday modern technology, and understanding their fundamental concepts can open doors to building and managing more accurate databases.

This article introduced you to what relational database constraints are, some of the different types, and how they’re enforced and violated. You should now have the essential knowledge to navigate the world of database constraints confidently.

If you’re curious to learn more, connect with me on LinkedIn, Twitter, or GitHub. Let’s continue this journey together toward mastering database systems!

Each certification is filled with hundreds of hours worth of interactive lessons, workshops, labs, and quizzes.

How Does the New Relational Databases Certification Work?

The new Relational Databases certification will teach you core concepts including Bash scripting, SQL, Git, and more.

The certification is broken down into several modules that include lessons, workshops, labs, review pages, and quizzes to ensure that you truly understand the material before moving onto the next module.

The lessons are your first exposure to new concepts. They provide crucial theory and context for how things work in the software development industry.

At the end of each lesson, there will be three comprehension check questions to test your understanding of the material from the lesson.

After the lesson blocks, you will do the workshops. These workshops are guided step-based projects that provide you with an opportunity to practice what you have learned in the lessons.

These workshops will not be done inside the regular freeCodeCamp editor in the browser. Instead you will need to do these workshops in one of three environments:

• GitHub Codespaces: This course runs in a virtual Linux machine using GitHub Codespaces.

• Your own local environment: This course runs in a virtual Linux machine on your computer.

• Ona: This course runs in a virtual Linux machine using Ona.

After the workshop, you will complete a lab which will help you review what you have learned so far. This will give you chance to start building projects on your own, which is a crucial skill for a developer. You will be presented with a list of users stories and will need to pass the tests to complete the lab.

At the end of each module, there is a review page containing a list of all of the concepts covered. You can use these review pages to help you study for the quizzes.

The last portion of the module is the quiz. This is a 20 question multiple choice quiz designed to test your understanding from the material covered in the module. You will need to get 18 out of 20 correct to pass.

Throughout the certification, there will be five certification projects you will need to complete in order to qualify for the exam.

Once you’ve completed all 5 certification projects, you’ll be able to take the 50 question exam using our new open source exam environment. The freeCodeCamp community designed this exam environment tool with two goals: respecting your privacy while also making it harder for people to cheat.

Once you download the app to your laptop or desktop, you can take the exam.

Frequently Asked Questions

Is all of this really free?

Yes. freeCodeCamp has always been free, and we’ve now offered free verified certifications for more than a decade. These exams are just the latest expansion to our community’s free learning resources.

What prevents people from just cheating on the exams?

Our goal is to strike a balance between preventing cheating and respecting people's right to privacy.

We've implemented a number of reliable, yet non-invasive, measures to help prevent people from cheating on freeCodeCamp's exams:

1. For each exam, we have a massive bank of questions and potential answers to those questions. Each time a person attempts an exam, they'll see only a small, randomized sampling of these questions.

2. We only allow people to attempt an exam one time per week. This reduces their ability to "brute force" the exam.

3. We have security in place to validate exam submissions and prevent man-in-the-middle attacks or manipulation of the exam environment.

4. We manually review each passing exam for evidence of cheating. Our exam environment produces tons of metrics for us to draw from.

We take cheating, and any form of academic dishonesty, seriously. We will act decisively.

This said, no one's exam results will be thrown out without human review, and no one's account will be banned without warning based on a single suspicious exam result.

Are these exams “open book” or “closed book”?

All of freeCodeCamp’s exams are “closed book”, meaning you must rely only on your mind and not outside resources.

Of course, in the real world you’ll be able to look things up. And in the real world, we encourage you to do so.

But that is not what these exams are evaluating. These exams are instead designed to test your memory of details and your comprehension of concepts.

So when taking these exams, do not use outside assistance in the form of books, notes, AI tools, or other people. Use of any of these will be considered academic dishonesty.

Do you record my webcam, microphone, or require me to upload a photo of my personal ID?

No. We considered adding these as additional test-taking security measures. But we have less privacy-invading methods of detecting most forms of academic dishonesty.

If the environment is open source, doesn't that make it less secure?

"Given enough eyeballs, all bugs are shallow." – Linus’s Law, formulated by Eric S. Raymond in his book The Cathedral and the Bazaar

Open source software projects are often more secure than their closed source equivalents. This is because a lot more people are scrutinizing the code. And a lot more people can potentially help identify bugs and other deficiencies, then fix them.

We feel confident that open source is the way to go for this exam environment system.

How can I contribute to the Exam Environment codebase?

It's fully open source, and we'd welcome your code contributions. Please read our general contributor onboarding documentation.

Then check out the GitHub repo.

You can help by creating issues to report bugs or request features.

You can also browse open help wanted issues and attempt to open pull requests addressing them.

Are the exam questions themselves open source?

For obvious exam security reasons, the exam question banks themselves are not publicly accessible. :)

These are built and maintained by freeCodeCamp's staff instructional designers.

What happens if I have internet connectivity issues mid-exam?

If you have internet connectivity issues mid exam, the next time you try submit an answer, you’ll be told there are connectivity issues. The system will keep prompting you to retry submitting until the connection succeeds.

What if my computer crashes mid-exam?

If your computer crashes mid exam, you’ll be able to re-open the Exam Environment. Then, if you still have time left for your exam attempt, you’ll be able to continue from where you left off.

Can I take exams in languages other than English?

Not yet. We’re working to add multi-lingual support in the future.

I have completed my exam. Why can't I see my results yet?

All exam attempts are reviewed by freeCodeCamp staff before we release the results. We do this to ensure the integrity of the exam process and to prevent cheating. Once your attempt has been reviewed, you'll be notified of your results the next time you log in to freeCodeCamp.org.

I am Deaf or hard of hearing. Can I still take the exams?

Yes! While some exams may include audio components, we do make written transcripts available for reading.

I am blind or have limited vision, and use a screen reader. Can I still take the exams?

We’re working on it. Our curriculum is fully screen reader accessible. We're still refining our screen reader usability for the Exam Environment app. This is a high priority for us.

I use a keyboard instead of a mouse. Can I navigate the exams using just a keyboard?

This is a high priority for us. We hope to add keyboard navigation to the Exam Environment app soon.

Are exams timed?

Yes, exams are timed. We err on the side of giving plenty of time to take the exam, to account for people who are non-native English speakers, or who have ADHD and other learning differences that can make timed exams more challenging.

If you have a condition that usually qualifies you for extra time on standardized exams, please email support@freecodecamp.org. We’ll review your request and see whether we can find a reasonable solution.

What happens if I fail the exam? Can I retake it?

Yes. You get one exam attempt per week. After you attempt an exam, there is a one-week (exactly 168 hour) “cool-down” period where you cannot take any freeCodeCamp exams. This is to encourage you to study and to pace yourself.

There is no limit to the number of times you can take an exam. So if you fail, study more, practice your skills more, then try again the following week.

Do I need to redo the projects if I fail the exam?

No. Once you’ve submitted a certification project, you do not need to ever submit it again.

You can re-do projects for practice, but we recommend that you instead build some of our many practice projects in freeCodeCamp’s developer interview job search section.

What happens if I already have the old Legacy Responsive Web Design certification? Should I claim the new one?

The new certification has more theory and practice as well as an exam. So if you’re looking to brush up on your skills, then you can go through the new version of this certification.

What will happen to my existing coursework progress on the Full Stack Certification? Does it transfer over to the Responsive Web Design course?

If you’ve already started the Certified Full Stack Developer Curriculum, all of your previously completed work should already be saved there.

To be clear, we’ve copied over all of the coursework from the full stack certification to this newer certification.

Can I still continue with the current Full Stack Developer Certification and just not do the new certification?

We’ve moved the coursework for the Full Stack Developer Certification over and broken it up into smaller certifications. Currently there are seven courses available for you to go through. Here is the complete list:

• Responsive Web Design Certification

• JavaScript Certification

• Frontend Libraries Certification

• Python Certification

• Relational Databases Certification

• Backend JavaScript Certification

• Certified Full Stack Developer Certification

The Certified Full Stack Developer Certification button will remain on the learn page for a short time to give people the opportunity to switch over to the new certifications. Over the next few months, though, this option will disappear.

Will my legacy certifications become invalid?

No. Once you claim a certification, it’s yours to keep.

Also note that we previously announced that freeCodeCamp certifications would have an expiration date and require recertification. We don’t plan to implement this anytime soon. And if we do decide to, we will give everyone at least a year’s notice.

Will the exam be available to take on my phone?

At this time, no. You’ll need to use a laptop or desktop to download the exam environment and take the exam. We hope to eventually offer these certification exams on iPhone and Android.

I have a disability or health condition that is not covered here. How can I request accommodations?

If you need specific accommodations for the exam (for example extra time, breaks, or alternative formats), please email support@freecodecamp.org. We’ll review your request and see whether we can find a reasonable solution.

Anything else?

Good luck working through freeCodeCamp’s coursework, building projects, and preparing for these exams.

Happy coding!

But what exactly is a database, and how does it work? This article will give you a foundational understanding of core DB concepts like:

• What databases are

• Different database models

• Database Management Systems (DBMS)

• How Relational Database models work

• Basics of Structured Query Language (SQL)

Whether you’re a beginner or just looking to refresh your knowledge, this article will help you learn the essentials.

What Is A Database?

A database is a collection of information – information that’s preferably related, and preferably organised. This means that a database can be in any form or shape. It could be a pile of paper records in an office, or a large Excel sheet, or on a computer (most likely in this day and age). But in the most basic terms, a database just helps you store data – so ultimately you can decide what it is.

In the digital world, a database consists of physical files on your computer, or in a cloud computer. These files are installed (or downloaded) when you set up the database software on your computer.

A database allows you to record, organise, manage, retrieve, and update that data efficiently. A database is usually structured, organised, and containing related information, otherwise it will just be a pile of random data.

The structure of a database consists of two main parts, the data and the metadata.

• data is the actual information stored in the database. So for example, a database of football players would contain information about players like their names, ages, clubs, and so on.

• metadata is the structural description of the data in a database. It describes the names of fields used to store data, the length of those fields (where applicable), and their datatypes. metadata gives structure and organisation to raw data.

How to Update a Database

You can make changes to the different parts of a database using various commands. There are two general types of commands:

Data Definition Language (DDL)

First, we have Data Definition Language, or DDL. It’s made up of commands that define or alter the shape or structure of the data in the database. These commands affect the metadata part of a database.

You might make alterations like creating new tables in a relational database, changing the shape of documents in a document-based database by adding new fields, or removing an entire graph in a graph database. DDL might define a field as a specific data type, for example, the "date" type, ensuring only valid dates can be entered.

Data Manipulation Language (DML)

We also have Data Manipulation Language, or DML. It’s made up of commands that interact with the data stored in the database. These commands do not affect the structure of the data, but rather the data itself. These command only affect the data part of a database.

Some of the things you can do with DML include reading data from a database, adding new data to the database, editing data, and deleting data.

Applications like TablePlus let you see the data and the metadata in a database. For example, the data and metadata parts of a football application might look like the images below, respectively:

A datatype defines what type of information can be stored in a field. Datatypes help computers understand how to store, process, and use data efficiently.

So a field in a table with a datatype of date will only be able to store date records, and will throw an error if you try to store something else, like a name. The same goes for a field with a number datatype – it will only accept numbers, and can be set to accept values within a range or to a certain number of decimal points.

Common datatypes include varchar for data that might contain different characters (text + numbers), date for date values, int for whole numbers, and so on. You can find other common database datatypes here.

What Is a Database Model?

A database model is a concept used to describe the information stored on a database. Think of it as a building’s blueprint designed by an architect. It details all the tables, columns, and datatypes of the database. But it, in itself, is not a physical entity like the database. A database model determines how data is logically represented and accessed.

Database models define if data is stored in tables using rows and columns, or in JSON-like objects. They also define how data relates, how you can query it, and how you manage it. Database models are often chosen (and often developed) to suit specific data/application needs.

Popular database models:

Relational Model: The Relational Model is the most popular database model. This model uses tables with rows and columns to store data. This model uses the SQL language to manage the data.

Examples of some relational databases include MySQL, PostgreSQL, and SQLite. This model is popularly used for general-purpose applications that require structured, and often related, data and complex queries. The rest of the article will focus on this model.

Document Model: Data is stored as documents, often in JSON or XML format, in this model. Databases like MongoDB and CouchDB use this model. Document DBs promote flexibility with their JSON-like structures, and they’re commonly used in applications dealing with semi-structured data or hierarchical data, where flexibility is key.

Key-Value Model: In this model, data is stored as simple key-value pairs, like in a map in programming. This model is used by Redis and DynamoDB. Due to the simplicity of this model, it is used in high-performance scenarios for simple lookups or caching.

Graph Model: this model uses nodes (entities) and edges (relationships) to manage data. Neo4j and Amazon Neptune are examples of databases using this model. The shape of the nodes and edges in the graph model make it a common choice in applications involving relationships or connections between data points.

There are many more database models. You can find and study them here if you’d like more info.

How Do Relational Databases Work?

A relational database has the ability to establish links – or relationships – between information by joining tables, which makes it easy to understand and gain insights about the relationship between various data points. - Google

The relational database model was developed as an improvement to an older database model, the Hierarchical Database model. Relational databases build on it and improve some of its restrictions and relationships. The tables in a relational DB model are often called relations.

Each row in a database table represents a single record in the table. The row tells the full story of the data. It contains data for all the columns in that table for one specific entity.

For example, in a table storing information about football players, each row represents one player and will include player details like name, age, country, and so on. Rows are also sometimes referred to as records or tuples in database terminology.

Each column lists an attribute of the record in question, such as name, age, or country. The column only tells a small part of the story. Each column has a name and a datatype, and it applies to all rows in the table. These columns could also have constraints in addition to their datatypes. These constraints could be as simple as the NOT NULL constraint that says that the column can not be empty on any row, or as complex as you define it.

For example, in a table of football players, columns might include “name”, “age”, and “country”. All rows in the table will have values under these columns for their respective attributes. In some contexts, columns are also referred to as fields.

The “relational” part of the name Relational Databases is often attributed to the fact that this model focuses on how data relates with other data, and how tables relate to each other. For example, tables can be linked (related) together. Tables can also be independent.

Despite this flexibility with relationships, the data in a table can be accessed directly without having knowledge of related or unrelated tables. You can easily access records as long as you know what you’re looking for. Primary and Foreign keys are used in the relational model to manage these relationships.

What Is a DataBase Management System (DBMS)?

A DataBase Management System (DBMS) is a collection of programs for managing and communicating with an underlying database engine. In simpler terms, a DBMS is the database engine coupled with whatever additional tools that come with it.

A DBMS helps you create, manage, and use databases. It provides an abstraction over the database engine and lets you more easily store, update, and retrieve data in a secure way.

The tools that come in a DBMS can include, but are not always limited to:

• frontend tools (like a query interface, or an administration panel) that help you run queries and visualise the resulting data in the database

• backup and recovery tools that work in the background with little to no user interaction

• security tools for user access management (roles and permissions)

• and data import or export tools.

And as you would imagine, DBMS are usually model-specific, so there are DBMS focused on the Relational Database Model called RDBMS, where the “R” is for Relational. Examples of popular RDBMS include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server. RDBMS use SQL (Structured Query Language) to interact with the data.

SQL Basics

Structured query language (SQL) is a programming language for storing and processing information in a relational database. You can use SQL statements to store, update, remove, search, and retrieve information from a database. You can also use SQL to maintain and optimise database performance. - Amazon

It serves as the primary interface for interacting with databases, allowing users to perform various operations such as creating, modifying, querying, and deleting data and database structures. It’s the base upon which RDBMS like MySQL, PostgreSQL, and SQLite are built, with their own optimisations and extensions.

In this section, we’ll take a look at some basic SQL commands, with practical examples.

DDL Commands

1. CREATE

This is the SQL command used to create and define new database objects. It's a part of the Data Definition Language (DDL), and its primary function is to establish the structure or schema of the database.

You can use this command to do the following (amongst many other uses):

• Create new databases

• Create new tables

• Create a new index in a table

• Create views

• Create a user with specific access rights

CREATE is most commonly used however to create a table in a database, or to create the database itself (although you usually do this using the GUI options the RDBMS provides).

This command has the following structure:

CREATE OBJECT_TYPE object_name (optional_further_arguments)

The ENTITY_TYPE is a placeholder and could be DATABASE, TABLE, VIEW, and so on from the list of database objects. The entity_name defines the name for the object being created. And finally, the optional_further_arguments is used to show that some of the objects only need a name to be created, while others like tables need more context about the columns of the table.

So based on our example of a football application, creating the football_db database above would involve first creating the database, like this:

CREATE DATABASE football_db;

This command creates a new database with the provided name, football_db. Next up, using the CREATE command followed by the object type TABLE, you can create a players table, like this:

CREATE TABLE `players` (
    `id` int PRIMARY KEY AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `age` int NOT NULL,
    `country` varchar(100) NOT NULL,
    `level` enum('Academy', 'Amateur', 'SemiPro', 'Professional') NOT NULL,
    `position` enum('Goalkeeper', 'Defender', 'Midfielder', 'Striker') NOT NULL,
    `foot` varchar(6) NOT NULL,
    `club` varchar(100) NOT NULL,
    `scores` json NOT NULL,
    `jerseyNumber` int NOT NULL
);

The command creates a table called players, and defines the columns (id, name, age, country, level, position, foot, club, scores, jerseyNumber) with their datatypes (int, varchar, enum, json). It also defines their constraints (PRIMARY KEY, AUTO_INCREMENT, NOT NULL).

2. ALTER

This command modifies the structure of an existing table. This command is versatile and allows for a wide range of table modifications. These include adding, removing, modifying, and renaming columns, and managing constraints and indexes.

To add a new height column to the newly created players table, you can use the ALTER command like this:

ALTER TABLE players
ADD height INT NOT NULL;

The command runs successfully and the new column, defined as an integer column, gets added.

3. DROP

This command deletes an existing table or database. When you use the DROP command, it completely removes the object from the database, and this action is irreversible. You can use us to remove databases, tables, and indexes.

If you ever stop using the players table, you can easily delete it using the DROP command like this:

DROP TABLE players;

4. TRUNCATE

This command removes all data from a table while preserving its structure. This same result can be achieved using the DELETE DML command.

DML Commands

These DML commands are foundational for CRUD operations, which stand for Create, Read, Update, and Delete – the basic actions you perform with data in a database.

1. INSERT

Adds a new record to the database. This is the Create part of CRUD.

The command has the structure like this:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The INSERT INTO is the first part of the query, it is mandatory and followed by the name of the table to insert into. The name of the table to insert into is represented by the table_name placeholder. The name can then be followed by a list of columns to populate, or the VALUES keyword. In case when the columns to populate are listed, the list of values has to have the same length as the length of the columns provided, as each entry in both lists will be mapped. In case when the columns to populate are not listed, the items in the values list are mapped to the database columns, and every column will have to be provided. The command also allows for the insertion of multiple records at the same time, following same rules as singe insertions, just with comma-separated multiple lists of values to insert.

To add a few players to the players table to achieve a similar result as in the first screenshot, you can use a couple of insert commands like this:

INSERT INTO
    `players` (`id`, `name`, `age`, `country`, `level`, `position`, `foot`, `club`, `scores`, `jerseyNumber`, `height`)
VALUES
    (1, 'Christiano Ronaldo', 36, 'Portugal', 'Professional', 'Striker', 'Right', 'Manchester United', '\"4, 3, 5, 2, 4\"', 7, 187),
    (2, 'Alisson Becker', 31, 'Brazil', 'Professional', 'Goalkeeper', 'Right', 'Liverpool', '\"5, 6, 7, 8, 9\"', 1, 193),
    (3, 'John Stones', 30, 'England', 'Professional', 'Defender', 'Right', 'Manchester City', '\"4, 5, 6, 7, 8\"', 5, 188),
    (4, 'Kevin DeBruyne', 33, 'Belgium', 'Professional', 'Midfielder', 'Right', 'Manchester City', '\"9, 8, 7, 6, 5\"', 17, 181),
    (5, 'Erling Haaland', 24, 'Norway', 'Professional', 'Striker', 'Right', 'Manchester City', '\"10, 9, 8, 7, 6\"', 9, 194),
    (6, 'Chris Waddle', 20, 'England', 'SemiPro', 'Midfielder', 'Left', 'Tow Law Town', '\"3, 4, 5, 6, 7\"', 11, 183),
    (7, 'Ian Wright', 25, 'England', 'SemiPro', 'Striker', 'Right', 'Greenwich Borough', '\"4, 5, 6, 7, 8\"', 8, 175),
    (8, 'Charlie Austin', 34, 'England', 'SemiPro', 'Striker', 'Right', 'Poole Town', '\"5, 6, 7, 8, 9\"', 9, 188),
    (9, 'Troy Deeney', 33, 'England', 'SemiPro', 'Striker', 'Right', 'Chelmsley Town', '\"6, 7, 8, 9, 10\"', 9, 183),
    (10, 'Rickie Lambert', 39, 'England', 'SemiPro', 'Striker', 'Right', 'Macclesfield Town', '\"7, 8, 9, 10, 11\"', 9, 187);

2. SELECT

The SELECT command has the basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The command has the following parts:

• SELECT is the mandatory keyword that begins every query,

• column1, column2, … is a placeholder for the list of columns to be retrieved. This is especially useful when dealing with large tables as you do not always want to display all columns every time. To display all columns, replace the list with the * character.

• FROM is another mandatory keyword that is followed by the name of the table to fetch the data from,

• table_name is the name of the table the data should come from.

• WHERE condition is one of the optional commands that can be attached to the SELECT command. It is used to filter the records by specific conditions.

This is the Read part of CRUD. The simplest form of the SELECT command is used to view all records in a table (all columns and rows):

SELECT * FROM players;

3. UPDATE

The UPDATE command modifies existing records in the database. This is the Update part of CRUD.

To update the details of Christiano Ronaldo to be more accurate, you can use the UPDATE command like this:

UPDATE `players`
SET
    `name` = 'Cristiano Ronaldo',
    `age` = 38,
    `club` = 'Al Nassr'
WHERE
    `id` = 1;

This command changes his name slightly, his club from Manchester United to his current club of Al Nassr, and updates his age to 38.

4. DELETE

The DELETE command removes records from the database. This is the Delete part of CRUD.

It is syntactically similar to the SELECT command, having a basic syntax like this:

DELETE FROM table_name
WHERE condition;

In this structure,

• The DELETE FROM keyword is the mandatory start of any delete queries,

• It is followed by the name of the table to be deleted, represented by the table_name.

• The WHERE condtion is optional where all the rows of the table are to be deleted. But otherwise, it is used to specify the rows to be deleted by matching a condition.

To remove players not playing at the professional level from the table, you can use a command like this:

DELETE FROM `players`
WHERE `level` != 'Professional';

These are the basic commands you’ll use to interact with databases. You can learn more about them in this SQL command cheatsheet.

Summary

Databases are a cornerstone of modern technology, and understanding their fundamental concepts can open doors to building and managing efficient, data-driven systems.

This article introduced you to the basics of what a database is and how relational database models work. You should now have the essential knowledge to navigate the world of databases confidently.

To deepen your understanding, consider exploring the following:

• Hands-On Practice: Use tools like TablePlus to interact with relational databases.

• Learning SQL: Start with beginner-friendly SQL tutorials like this course on freeCodeCamp’s YouTube channel or this SQL command cheatsheet.

• Experiment with Non-Relational Databases: Try MongoDB or Neo4j to explore how other database models work.

If you’re curious to learn more, connect with me on LinkedIn, Twitter, or GitHub. Let’s continue this journey together toward mastering database systems!

During this full-length 300-hour course, you will learn to build more than a dozen projects. Some of them will involve step-by-step instructions, and others will be open ended, with elaborate test suites.

You will use real developer tools and software like VS Code, PostgreSQL, and the Linux / Unix command line to complete interactive tutorials and build projects.

What you will learn

• The Linux / Unix Command Line

• Relational Databases

• SQL and PostgreSQL

• Bash and Bash Scripting

• Git and GitHub

• Nano

• And a lot of other concepts and tools

This course was made possible by a grant from Class Central, a search engine and review site for online courses.

How to install Docker and run the Relational Database Curriculum

Docker will run a container on your computer that has the software and file structure required for these tutorials.

You will be working in that container using VSCode and the Dev Containers extension. Once it's running, the CodeRoad extension will run the tutorials we have created.

Prerequisites

Before you get started, you need to have a few things installed:

1. The Docker Engine

2. VS Code

3. The Dev Containers extension for VS Code

4. Git

How to Run a Project in Docker

Follow these steps to run the docker container and start a tutorial:

1. Clone the RDB Alpha repo to your computer with git clone https://github.com/freeCodeCamp/rdb-alpha

2. Open a terminal, navigate to the rdb-alpha directory, and open VS Code with code .

3. In VS Code, open the command palette with Ctrl / Cmd + Shift + P. Then, enter and run Dev Containers: Rebuild and Reopen in Container

4. A new VS Code window will open and begin building the Docker image. It will take several minutes to build the first time

5. Once the image is finished building, open the command palette again with Ctrl / Cmd + Shift + P, enter and run CodeRoad: Start. The command won't be available until the extension has finished installing in your container

6. In the CodeRoad window, click "Start New Tutorial"

7. Click the URL tab and enter the URL of the tutorial.json file for the project you want to start (ex: https://raw.githubusercontent.com/freeCodeCamp/learn-bash-by-building-a-boilerplate/main/tutorial.json) Full list of available tutorials below.

8. Click the "Start" button to start the lessons

How to Restart or Switch Projects

If you restart or switch projects, you will lose your progress on a tutorial you may have started along with any files or folders you may have created.

1. Open the command palette with Ctrl / Cmd + Shift + P, enter and run Dev-Containers: Rebuild Container

2. Wait for VS Code to reopen the reload the container

3. Open CodeRoad from the command palette like you did before, click "Start New Tutorial", and enter the URL of the tutorial.json file for the project you want to do

Available Courses

Here is a list of tutorials currently available. Open one of them and use its URL, as described in the instructions above, to start it.

• Learn Bash by Building a Boilerplate

• Learn Relational Databases by Building a Database of Video Game Characters

• Celestial Bodies Database

• Learn Bash Scripting by Building Five Programs

• Learn SQL by Building a Student Database: Part 1

• Learn SQL by Building a Student Database: Part 2

• World Cup Database

• Learn Advanced Bash by Building a Kitty Ipsum Translator

• Learn Bash and SQL by Building a Bike Rental Shop

• Salon Appointment Scheduler

• Learn Nano by Building a Castle

• Learn Git by Building an SQL Reference Object

• Periodic Table Database

• Number Guessing Game

Here's a video of me doing "Learn Bash by Building a Boilerplate" in 13 minutes and 38 seconds:

Also, Download the freeCodeCamp Dark Theme for VS Code

If you like the color scheme that these tutorials use, you can download the freeCodeCamp Dark Theme extension from the Visual Studio Marketplace.

You can learn more about the Dark Theme here.

Help us improve these courses by asking questions and giving us feedback

If you have any questions about these new Relational Database courses, get stuck at some point, or just have general feedback about them, you can create a thread on the freeCodeCamp Forum.

We also have our own Slack-like chat room system where you can ask questions and help contribute to our open source projects. Join our chat room system.

Happy Coding.