I can't understand SQLite

Question

So I've been learning a lot of SQL in school using MySQL , Oracle and SQL Server. So now i need to work with SQLite on a raspberry pi project. The thing I don't understand is datatypes. When you put your field to be int it can only save whole numbers right? And in SQLite it can save integers inside of a int datatype. So i have been reading about it and how SQLite saves data in these groups:

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB. The value is a blob of data, stored exactly as it was input.

So it only makes a difference when saving data and not on input? I can't understand it.. How can I make my INT columns not accept integers? Or it isn't possible?

Answer 1

The SQLite dialect of SQL is a dynamically-typed language. Just like Python will let you create an array of mixed types like [None, 42, 3.14, 'Hello, world!', b'\x01\x23'], SQLite will let you store values of multiple types in the same column.

Storage classes

Conceptually, SQLite has five scalar data types, called “storage classes”:

• The NULL type is used for the singleton value NULL, which is a sentinel value indicating an unknown or not-applicable value.
• The INTEGER type is a 64-bit signed integer. The part about “stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value” is an implementation detail of the file format, and not something you as a programmer need to be concerned with. From the point of view of the C API and the SQL language, it's always 8 bytes.
• The REAL type is a 64-bit floating-point number.
• The TEXT type is a character string.
• The BLOB type is a sequence of octets. SQL literals are X-prefixed strings of hex digits (e.g., X'0123456789ABCDEF').

If you're a Python 3.x programmer, you can think of:

• NULL = None (of type NoneType)
• INTEGER = int (but limited to 64 bits)
• REAL = float
• TEXT = str
• BLOB = bytes

(This is actually the type mapping used by Python's standard sqlite3 module.)

If you're a C# programmer, you can think of:

• NULL = null (but without the arbitrary distinction between value types and reference types)
• INTEGER = long
• REAL = double
• TEXT = string
• BLOB = byte[]

SQLite lets you store a value of any data type in any column. (Exception: If a column is declared as INTEGER PRIMARY KEY, and its table is not declared WITHOUT ROWID, then it actually is constrained to contain only integers, because it is an alias for the row ID.)

Column type affinity

Independent of the above list of “storage classes”, SQLite has the concept of type affinity which determines the preferred data type to store in a column. The affinitity of a column is determine by its declared datatype.

Only numeric (INTEGER and REAL) and TEXT values are affected by these automatic type conversions; there is never any implicit conversion to or from the NULL or BLOB storage classes.

So, if you declare a column with integer affinity (e.g., INT or INTEGER), but you insert a text string into it, then SQLite will automatically convert the value to an integer for you. But if it can't, it leaves it as a string. So, the string '42' becomes the integer 42, but the string 'xyz' stays as-is.

Real-affinity columns (REAL, FLOAT, DOUBLE) are similar, except that they attempt to convert strings into REAL values instead of INTEGER values. Numeric-affinity columns (NUMERIC, DECIMAL) convert strings to numbers, but will happily allow INTEGER and REAL values in the same column.

Text-affinity columns (CHAR, VARCHAR, TEXT, CLOB) will make the opposite conversion: All numbers (integer or real) are converted to strings.

Blob-affinity columns (BLOB or no declared data type) leave all values as-is.

Answer 2

The actual value determines the type.
Similar to Excel.

You don't even have to define types for columns, e.g. -

create table t (c1,c2,c3);

https://www.sqlite.org/datatype3.html

1. Datatypes In SQLite

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

---

drop table if exists t;
create table t (studentId int);
insert into t (studentId) values (1),(2),('Say what?'),(3),('Yep!');
select studentId from t;

sqlite> drop table if exists t;
sqlite> create table t (studentId int);
sqlite> insert into t (studentId) values (1),(2),('Say what?'),(3),('Yep!');
sqlite> select studentId from t;
studentId
----------
1
2
Say what?
3
Yep!

sqlite> .header on
sqlite> drop table t;
sqlite> create table t (studentId text);
sqlite> insert into t (studentId) values (1),(2),('Say what?'),(3),('Yep!');
sqlite> select studentId,studentId * 10 from t;
studentId   studentId * 10
----------  --------------
1           10
2           20
Say what?   0
3           30
Yep!        0

Answer 3

No, it is wrong to say that in SQLite if you declare an INT column it will only accept integer values. In SQLite, you can store any datatype in any column. Therefore, you can declare the column AGE INTEGER and store the value 'I am pretty old', of type TEXT in that column.

This is quite different from virtually every other relational database, which requires the type of the value to match the type of the column it is stored in.