Why columns with integer data type is also storing the float values in SQL data base

Question

I made a SQL database using this statement

final String SQL_CREATE_PRIMARY_TABLE = "CREATE TABLE " +
            TABLE_NAME + " (" +
            P_K + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            SET_NAME + " TEXT, " +
            MARKS_O1 + " INTEGER, " +
            MARKS_O2 + " INTEGER, " +
            MARKS_O3 + " INTEGER, " +
            MARKS_O4 + " INTEGER, " +
            ATTEMPTS + " INTEGER " +
            ")";

As you can see that column MARKS_01 to MARKS_04 has INTEGER data type, but accidentally I provided a float type to it which have getter and setter like this:-

public float getMO1() {
    return MO1;
}
public void setMO1(float MO1) {
    this.MO1 = MO1;
}
public float getMO2() {
    return MO2;
}
public void setMO2(float MO2) {
    this.MO2 = MO2;
}
public float getMO3() {
    return MO3;
}
public void setMO3(float MO3) {
    this.MO3 = MO3;
}
public float getMO4() {
    return MO4;
}
public void setMO4(float MO4) {
    this.MO4 = MO4;
}

public int getAttempts() {
    return Attempts;
}

public void setAttempts(int attempts) {
    Attempts = attempts;
}

And, end results are surprising for me even with a integer data type it is working perfectly for float values

I want to know the reason behind it, either it is a property of SQL database or any bug

And, can I use this property/bug as a plus point for my other databases

Because SQLite [doesn't really have](https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=5fc837dff89b051ef9728ba503f802a3) different data types. Everything is stored as a string. You could store `awesome` in those columns as well — , Apr 15 '21 at 14:56
From: https://www.sqlite.org/datatype3.html#storage_classes_and_datatypes: *Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class.* — forpas, Apr 15 '21 at 14:58
@a_horse_with_no_name Then what is the significance of other data types in SQLite — PRANAV SINGH, Apr 15 '21 at 14:59
No idea. You can leave them out if you want. Probably just intended for documentation purposes. — , Apr 15 '21 at 15:00
Read about SQLite's "Type Affinity" here: https://www.sqlite.org/datatype3.html#type_affinity where it is mentioned that: *the type is recommended, not required*. — forpas, Apr 15 '21 at 15:04

score 2 · Accepted Answer · answered Apr 15 '21 at 20:11

I want to know the reason behind it, either it is a property of SQL database or any bug

SQLite has been designed with flexibility and it's data types are flexible to the extent that any type of data can be stored in any type of column. The exception being that the rowid or an alias of the rowid must be an integer.

unless a table is DEFINED using WITHOUT ROWID a table always has a rowid column if a column is DEFINED using INTEGER PRIMARY KEY (with or without AUTOINCREMENT) or implicitly with INTEGER PRIMARY KEY then that column is an alias of the rowid (e.g. your P_K column is an alias of the rowid)
- If you used SELECT *,rowid FROM your_table you would see the P_K column and the rowid column (both the same value) in the result (as well as all other columns).

I believe that this flexibility allows relatively simple use of SQL from other databases. e.g. VARCHAR(100) will result in TEXT affinity.

As such there is no bug, the flexibility is intentional.

And, can I use this property/bug as a plus point for my other databases

Sometimes have you to consider and cater for the flexibility which can be a disadvantage to some. The plus point of how datatypes are handled is largely behind the scenes in that SQLite stores data efficiently.

perhaps one of the most recurring issues people face is using DATETIME as a data type this actually results in an affinity of NUMERIC (the catchall for none of the other rules). People then often think this converts the data into a time based type which automatically converts data (it doesn't).

A lot can be learned from reading Datatypes in SQLite Version 3

How flexible/restricive are SQLite column types? discusses the flexibility with some examples from an Android perspective.

Why columns with integer data type is also storing the float values in SQL data base

1 Answers1