CODEWITHSUNDEEP
c#sqlsqliteboolean
Andy Orchard
Andy Orchard

Reputation: 15

How to store boolean values in SQLite

I am currently designing a WPF application using a SQLite database. As part of this form, users are asked whether they have any disabilities and can check a couple of options in a list, such as No disability, Leg problems, heart issues etc. This will all go into a database that has a Student table. Currently my Student table SQL code is as follows:

CREATE TABLE STUDENT(
    StudentID int(5),
    firstName string(256),
    ...,
    age int(3)
);

The issue I am having is not knowing how to store this in the database. I know SQLite does not support Boolean values as this was my initial idea. If the box was selected then the value would be set to True and vice versa. In the absence of Boolean values, how would I go about storing this data in a database?

Upvotes: 1

Views: 11917

Answers (3)

Chris Chiesa
Chris Chiesa

Reputation: 21

Judging from my own experience here a second ago, the widespread claim that SQLite stores BOOLEANs as integers having the value 0 or 1, is at best mere hearsay, and at worst an outright falsehood.

Here, using SQLiteStudio 3.0.6 anyway, I created a table with a field that the software swears is BOOLEAN, but which can take on pretty much any value I care to assign it, including random strings. These are reliably stored into the backing .db file and appear in query results performed in entirely separate invocations of the SQLiteStudio GUI and/or CLI tools.

I always leave room for the possibility that I am somehow doing something wrong, or that the somewhat "older" version of SQLiteStudio I'm using contains a significant bug in this area -- but it sure looks like BOOLEANs are being stored as strings -- and, a much greater concern are not being constrained to the expected values of only 0 or 1, a.k.a. TRUE or FALSE.

Testing with Perl code, using the DBI module with the SQLite driver, random string values stored in fields that are supposedly BOOLEAN are in fact delivered as the exact string values that have been stored. No conversion to BOOLEAN as one might expect, and no complaint about, or enforcement of, violations against the two-statedness that is supposed to be the whole point of BOOLEAN.

Upvotes: 0

Renatas M.
Renatas M.

Reputation: 11820

You can store boolean value in int(1) where 0 represent false and 1 - true.

See Data types in SQLite documentation there is separate section 2.1 Boolean Datatype:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Upvotes: 2

Cesar
Cesar

Reputation: 527

As you previously said, SQLite doesn't support BOOLEAN.

You have to store it as an INT.

1 will be TRUE

0 will be FALSE

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

You can check the documentation here

Upvotes: 1

Related Questions