SQLite does not check Data Types

Question

Dear Stack Overflow Community,

I have a little problem here with starting my first attempt to Database-Design.

I try to make some Kind of a tabular and try to check out the Basic functions; writing with Python3 and SQLite3.

My question is quite simple, I have a few lines:

cur.execute('CREATE TABLE IF NOT EXISTS test(a INTEGER, argument DATE)')

Now I can just write:

cur.execute('insert into test (a, argument) values (?,?)', "bla", "blabla")

Without getting an error message; shouldn't this command be automatically forbidden by SQL when you define the allowed datatypes in the CREATE TABLE statement?

Full code is here: http://codepad.org/kf4gZB7m

Welcome to SO, Update your question with the code in the question — Mohamed ALOUANE
– Mohamed ALOUANE, Commented May 31, 2017 at 17:53

phd · Accepted Answer · 2017-05-31 14:23:05Z

5

This is a feature, not a bug. SQLite uses dynamic typing. See https://sqlite.org/faq.html#q3

answered May 31, 2017 at 14:23

phd

97.9k14 gold badges168 silver badges226 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

beinando Over a year ago

Okay thank you for the answer! Is this Feature connected to some Performance losses or higher memory consumption? And if yes, is there a way to disable this feature?

phd Over a year ago

Don't know about memory consumption but there is certainly no way to make SQLite more type-strict. You'd need a different SQL implementation for that.

CL. Over a year ago

SQLite often uses less memory and is faster than other DBs, but comparing embedded and client/servers DBs does not really make sense. You can add manual checks if you really need them.

Timmmm Over a year ago

It's a pretty terrible feature. There should at least be a way to turn on proper type checking. :-/

Marcos Pereira Over a year ago

@Timmmm there is now: sqlite.org/stricttables.html

groenhen · Accepted Answer · 2019-02-28 11:58:01Z

2

This feature is Type Affinity:

SQL database engines that use rigid typing will usually try to automatically convert values to the appropriate datatype. The important idea here is that the type is recommended, not required.

Docs: https://sqlite.org/datatype3.html#affinity

edited Feb 28, 2019 at 11:58

groenhen

3,02725 gold badges52 silver badges69 bronze badges

answered Feb 28, 2019 at 9:03

Toan Nguyen

1621 silver badge8 bronze badges

Comments

Kodiologist · Accepted Answer · 2023-10-15 19:21:44Z

As of version 3.37.0 (2021-11-27), SQLite supports a strict typing mode to fix its most infamous feature. It must be enabled per-table with the keyword STRICT.

> CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT) STRICT;
> INSERT INTO t VALUES (2, "foo");
> INSERT INTO t VALUES ("foo", "bar");
Runtime error: datatype mismatch (20)

For better or for worse, SQLite will still try to silently coerce values to match the column type even in strict mode:

INSERT INTO t VALUES ("1", 2);   -- A-okay!

However, if no lossless coercion is possible, like using "foo" as an integer above, an error is raised.

Collectives™ on Stack Overflow

SQLite does not check Data Types

3 Answers 3

5 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Related