How to Add Boolean Value in SQLite?

Boolean values are important in programming as they are used for decision-making processes in the application. The only two potential values for a logical value called a Boolean are true or false. Boolean values are used to represent a wide range of concepts such as the state of a switch, whether a user is logged in, or whether a task is completed or not. SQLite is a relational database management system that supports the storage and manipulation of a wide range of data types, including Boolean values.

This article explains how to add Boolean values in SQLite.

How to Add Boolean Value in SQLite?

You can add Boolean values in SQLite:

- Using BOOLEAN Data Type
- Using INTEGER Data Type

Method 1: How to Add Boolean Values using BOOLEAN Data Type in SQLite?

In SQLite, BOOLEAN data type can be used to add Boolean values in the table. You can use the following code to add a Boolean column in SQLite:

CREATE TABLE table1 (
flag BOOLEAN
);

Using the INSERT INTO statement, you can add data to the Boolean column after the table has been created.

INSERT INTO table1 (flag) VALUES (1);
INSERT INTO table1 (flag) VALUES (0);
INSERT INTO table1 (flag) VALUES (0);
INSERT INTO table1 (flag) VALUES (1);
INSERT INTO table1 (flag) VALUES (1);
INSERT INTO table1 (flag) VALUES (0);

Data from a Boolean column can be retrieved using the SELECT command. For instance:

SELECT * FROM table1 WHERE flag = 1;

With this, all rows from the table whose value in the Boolean column is true (expressed as the integer 1) would be returned.

Method 2: How to Add Boolean Values using INTEGER Datatype in SQLite?

Boolean values can also be represented in SQLite using the INTEGER data type. Boolean values can be represented using an INTEGER data type having a value of 0 or 1. This mapping enables the storage of Boolean values in an INTEGER column, where a value of 0 denotes false and a value of 1 denotes true.

When defining a table in SQLite, the column that will store Boolean values should be defined as an INTEGER with a CHECK constraint that limits the allowed values to 0 or 1. Only valid Boolean values are kept in the column due to the CHECK requirement.

For example, the following SQL statement creates a table with a column named is_active that can only store Boolean values:

CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT, is_active INTEGER CHECK (is_active IN (0,1)));

The following SQL statement inserts a row into the user table with a value of true for the is_active column:

INSERT INTO user (id, name, is_active) VALUES (1, 'Jade', 0);

Now let’s insert some more values.

INSERT INTO user (id, name, is_active) VALUES (2, 'Joe', 1);
INSERT INTO user (id, name, is_active) VALUES (3, 'Lane', 0);
INSERT INTO user (id, name, is_active) VALUES (4, 'Automn', 1);

To retrieve Boolean values from an SQLite table, use the SELECT statement. A SELECT statement that gets a Boolean column will return either 0 or 1 as the outcome. To interpret the result as a Boolean value, compare the result to the integer value that represents true. For instance, the is_active field from the user table is retrieved with the SQL statement that follows:

SELECT is_active FROM user WHERE id = 1;

Conclusion

Boolean values can be added in SQLite using the BOOLEAN datatype or the INTEGER data type. You can use a CHECK constraint to make sure that the column contains only valid Boolean values. This article has covered both these methods, allowing users to quickly add Boolean values in SQLite.

How to Add Boolean Value in SQLite?