CODEWITHSUNDEEP
sqlitecreate-tablecheck-constraints
synalice
synalice

Reputation: 358

No limitation on SQL column data type

I have SQLite database (created in SQLiteStudio).

The DDL is this:

CREATE TABLE player (
    player_id      INTEGER       PRIMARY KEY ASC AUTOINCREMENT
                                 UNIQUE
                                 NOT NULL,
    first_nickname STRING (10)   NOT NULL,
    health         INTEGER (100) NOT NULL,
    gold           INTEGER (99)  NOT NULL,
    silver         INTEGER (99)  NOT NULL,
    bronze         INTEGER (99)  NOT NULL,
    gerium         INTEGER (99)  NOT NULL
);

database_structure

Expected: I shouldn't be able to insert integers higher than 100 into health column.

What happens: For some reason, I can insert numbers higher than 100

Why was I able to...

INSERT INTO player (first_nickname, health, gold, silver, bronze, gerium) VALUES ('Foo', 101, 0, 0, 15, 0)

...if my health's column is set to INTEGER with max value of 100? Why am I able to insert 101 when I should only be able to enter 100?

sql_101

Upvotes: 1

Views: 900

Answers (1)

forpas
forpas

Reputation: 164089

When you define a column as INTEGER(100) or use any other value inside the parentheses, this does not create any constraint for the values of the column.
In fact SQLite totally ignores it.

SQLite does not even check the data type of the column, so you can insert not numeric strings in a column defined as INTEGER without any error.

The same applies to STRING(10) as there is no STRING data type in SQLite, but you should use TEXT.

All this is explained clearly in Datatypes In SQLite.

If you want to create such constraints you must explicitly define CHECK constraints, something like:

CREATE TABLE player ( 
  player_id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL, 
  first_nickname TEXT NOT NULL CHECK(LENGTH(first_nickname) <= 10), 
  health INTEGER NOT NULL CHECK(health BETWEEN 0 AND 100), 
  gold INTEGER NOT NULL CHECK(gold BETWEEN 0 AND 99), 
  silver INTEGER NOT NULL CHECK(silver BETWEEN 0 AND 99), 
  bronze INTEGER NOT NULL CHECK(bronze BETWEEN 0 AND 99), 
  gerium INTEGER NOT NULL CHECK(gerium BETWEEN 0 AND 99) 
);

See a simplified demo.

Upvotes: 2

Related Questions