In this video, you'll learn how to store boolean data in a database.
You'll see examples in Oracle, SQL Server, MySQL,
and PostgreSQL, with recommended solutions, and a few other solutions that are not recommended.
Let's get into it!
A boolean is a true or false value. We often need to store these in our database,
and there are several ways it can be done. How can we do it?
The answer depends on which database you're using.
We'll look at four databases in this video: Oracle, SQL Server, MySQL, and PostgreSQL.
If you're using a different database, the same principles should apply.
Let's start with Oracle.
In Oracle, there is not a specific boolean data type. Well, there is in PL/SQL,
which is Oracle's procedural language, but that can't be used when creating tables.
So, what do we do instead?
I recommend storing booleans as a NUMBER field with 1 digit.
This would store a value of 1 for true and 0 for false.
The SQL to create a table would look like this.
We've got a table called testbool, with a text value as the first column.
Our second column is called is_checked, and this is our boolean.
It has a data type of number with 1 digit, which is the number 1 inside the brackets.
You can use it how it is, or you can make it even better by adding a check constraint on the column.
This will ensure other values cannot be entered.
The check constraint will ensure that this is_checked can only store the values of 1 or 0.
This means you can't insert a value of 2, for example, which keeps our data clean.
Here are some valid INSERT statements. We insert a value of 1 for true and 0 for false.
We can select from the table, like this, and can see the results.
Using a number as a boolean is simple.
What about using a character column, such as VARCHAR2 or CHAR? I'll explain that solution,
and why I don't recommend it, later in this video, because it applies to all databases.
Next up we have SQL Server. Like Oracle, there is no boolean data type in SQL Server.
However, a common solution to this is to use a BIT data type. This is a very small
number field and can store the values from 1 to 64 in the format of ones and zeroes.
We can use it to store either 1 for true or 0 for false.
The SQL to create a table would look like this. We have a VARCHAR value here for the first column,
just for demo purposes. The second column is called is_checked and is a BIT type with 1 digit.
This means we can use it to store either 1 or 0. We don't need to add a check constraint to
limit the values to 1 or 0 because the BIT data type only accepts ones and zeroes.
Here are some Insert statements to add data to the table. We just specify 1 for true and 0 for false.
We can select from this table and see the values of 1 and 0.
In MySQL there is a boolean data type. However, it's just a synonym,
or another name for, the TINYINT data type which is a type of number field.
This means you can use it like a boolean where you store 1 or 0.
However, you would need to add a check constraint to ensure other digits aren't allowed.
Another way to do it is to use a BIT field. This works in the same way as SQL Server,
where the BIT field is used to store a value from 1 to 64 in the format of ones and zeroes. So,
you can have a BIT field with 1 digit and it can store either 1 or 0, without a check constraint.
Here's the SQL to create the table. Once again we have the is_checked column,
and it has a data type of BIT with one digit. This will store either 1 for true or 0 for false.
Here are the Insert statements to add data to that table. We can see that 1 and 0 are inserted.
When we select from the table, this is what we see.
There is a 1 and a 0 value, just like we inserted.
PostgreSQL actually does have a boolean data type, unlike the other databases we looked at.
It's a proper boolean type as well, which means it can handle true and false values.
Here's the SQL to create a table with it. We use the data type of boolean.
There's no need to specify a check constraint or a number of digits. Just the Boolean data type.
To insert data, we specify the value of TRUE or FALSE like this.
When we Select from the table, this is what we see.
Our two records are shown here with the values of true and false.
So, PostgreSQL is the only database of the four here that implements a boolean data type.
You might be wondering if we can just use a character data type instead of a number.
The answer is you could do that. You could use a VARCHAR field or VARCHAR2 in Oracle,
or even a CHAR field, to store a single-character value for true of false.
This could work.
But I don't recommend it for two reasons. One, it's a little harder to know what the
characters mean. If you put T or F for True or False it may not be immediately obvious that
T is True and F is False, especially if you see the value in another query or in an application.
Another reason is that T and F are English-specific.
For non-English languages and people, T and F may not be obvious.
The same thing can be said for Y or N as Yes or No.
It can still work, but may not be immediately obvious.
That's why I recommend using boolean where you can, like in PostgreSQL,
or using a single-digit number or BIT values for boolean.
And that brings us to the end of this video on storing boolean values in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit databasestar.com.
That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the fact that you can use numbers for
booleans, or the reasons why text values may not be the best choice, or something else?
Thanks for watching.