CODEWITHSUNDEEP
databasesqlitetextintegersqldatatypes
user7279819
user7279819

Reputation:

SQLite Column restriction: Only Integer

The following code lines illustrates the creation of table and where age column is created with data type INTEGER but still able to insert TEXT.

sqlite> .open dummy.db
sqlite> CREATE TABLE dummy1 (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER);
sqlite> INSERT INTO dummy1(name, age) VALUES ("varma", 40);
sqlite> INSERT INTO dummy1(name, age) VALUES ("sandy", sixty);
Error: no such column: sixty
sqlite> INSERT INTO dummy1(name, age) VALUES ("sandy", "sixty");
sqlite> SELECT * FROM dummy1;
1|varma|40
2|sandy|sixty

I tried creation of table with column restriction as : "age INTEGER"

Attempt to restriction failed.

Upvotes: 1

Views: 1175

Answers (2)

CL.
CL.

Reputation: 180060

SQLite uses dynamic typing; if you want to enforce column types, you have to use an explicit constraint:

CREATE TABLE dummy1 (
    ...,
    age INTEGER  CHECK (typeof(age) = 'integer')
);

Upvotes: 3

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

This is allowed in SQLite because SQLite has "dynamic type system", which means that the datatype is associated with what is stored instead of what is defined in the table definition. Everything is internally stored as TEXT.

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

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.

This is not allowed in other RDBMS such Oracle, SQL Server etc, where data must match the type of the column.

Upvotes: 0

Related Questions