Storing Booleans in SQLite: The Truth Behind the Ones and Zeros

2024-07-27

Storing Booleans as Integers:
- 0 represents False.
Creating a Table: You can use the INTEGER data type when creating a column to store these boolean values. While some database tools might show the column as BOOLEAN for readability, SQLite treats it as a regular integer.
Inserting and Retrieving Data:
- Use 1 to insert a true value and 0 for false.
- When retrieving data, you'll get 1 or 0 which you can interpret as true or false based on your logic.

Here's an example:

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  completed INTEGER NOT NULL DEFAULT 0  -- 0 (False) by default
);

INSERT INTO tasks (completed) VALUES (1); -- Inserting a completed task (True)

Things to Consider:

SQLite also accepts keywords TRUE and FALSE since version 3.23.0, but these are just treated as alternative ways to write 1 and 0.
You can use a CHECK constraint to enforce that the values in the column are always 0 or 1 for better data integrity.

-- Create a table with a column to store boolean values (represented by integers)
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  is_active INTEGER NOT NULL DEFAULT 0  -- 0 represents False (user inactive by default)
);

-- Insert data with boolean values (1 for True, 0 for False)
INSERT INTO users (id, is_active) VALUES (1, 1);  -- Active user
INSERT INTO users (id, is_active) VALUES (2, 0);  -- Inactive user

Selecting Data based on Boolean Value:

-- Select users who are active (is_active = 1)
SELECT * FROM users WHERE is_active = 1;

Updating Boolean Values:

-- Update a user to be active (is_active = 1)
UPDATE users SET is_active = 1 WHERE id = 2;

Using CHECK Constraint (Optional):

-- Create a table with a CHECK constraint to ensure values are 0 or 1
CREATE TABLE settings (
  id INTEGER PRIMARY KEY,
  show_notifications INTEGER NOT NULL CHECK (show_notifications IN (0, 1))  -- Enforce 0 or 1 only
);

-- Insert data following the constraint (1 for True)
INSERT INTO settings (id, show_notifications) VALUES (1, 1);

You can store "True" or "False" text strings instead of integers.
This might be useful if your programming language or framework has built-in functions for handling strings as booleans.
However, this method is generally less efficient in terms of storage space compared to integers.
Additionally, queries might become slightly more complex as you'd need to compare text values instead of simple integer comparisons.

Example:

CREATE TABLE preferences (
  id INTEGER PRIMARY KEY,
  enabled TEXT NOT NULL DEFAULT 'False'
);

INSERT INTO preferences (enabled) VALUES ('True');

Using Custom Flag Values (for specific scenarios):

In some cases, you might have more than two boolean states you want to represent.
For example, a flag indicating completion status could have values like "Pending", "Completed", or "Cancelled".
Here, you would define a custom data type (like TEXT) and store these distinct string values.
This method offers more flexibility but requires additional logic in your code to interpret the flag values.

Example (simplified):

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'Pending'  -- Pending, Completed, Cancelled
);

INSERT INTO tasks (status) VALUES ('Completed');

Choosing the Right Method:

Storing booleans as integers (0/1) is the recommended approach for most cases due to its efficiency and simplicity.
Use text strings if your programming language or framework heavily relies on string-based booleans.
Consider custom flag values only for scenarios with more than two distinct states and where interpreting those states is handled within your application logic.

sqlite