SQLite more laid back than the D-O-double-G

SQLite only supports a simple set of data types and the only one that really matters is "INTEGER PRIMARY KEY" so you can have it auto-increment. In fact, by default, I can declare the columns as anything I want and it doesn't even throw a warning.

sqlite> CREATE TABLE t2(c1 wtf, c2 yomama);
sqlite> INSERT INTO t2 VALUES(1, 'blah');
sqlite> SELECT * FROM t2;
1, blah

This is all documented and explained so I can't complain to much but it's still an interesting concept.


Is that a good thing? If I have a column and I know that I always want numbers with a certain decimal value in it, wouldn't I want to enforce that? What if you have a column that does not enforce it, am I now able to do do math on the values in it?

I don't know much about SQLite, and this does seem like it could be a good feature in some settings.
-- Orrin, 11 Nov 2008
It's confusing, I agree. The document I linked to says
The type affinity of a column is the recommended type for data stored in that column. The key here is that the type is recommended, not required. Any column can still store any type of data, in theory.

There is mention of a "Strict Affinity Mode" which I assume makes it act like every other database out there, but I didn't see a way to enable it or any other notes about it. My guess is it isn't implemented yet.

SQLite definitely has it's quirks but it's also filling a different niche than most other databases engines.
-- Wil Clouser, 11 Nov 2008
At first I was totally put off by this concept but now I love it. I was also put off by the lack of typed variables in perl at first, but now I love them too. It just takes some getting used to.

SQLite is an amazing piece of software, I highly recommend it.
-- Scott Baker, 11 Nov 2008

Post a comment

All comments are held for moderation; basic HTML formatting accepted.