Adding type constraints to tables

[GoogleCodeExporter]

I wish to add type constraints to tables. The following CREATE TABLE SQL query 
with a CHECK is adapted from SQLite The Definitive Guide:

SQLiteConnection db = new SQLiteConnection();
db.open(true);
SQLiteStatement statement = db.prepare("CREATE TABLE MyTypes (s TEXT 
CHECK(typeof(s)='TEXT'));");
statement.step();
statement = db.prepare("INSERT INTO MyTypes VALUES ('Hello');");
statement.step();

It fails with the following SQLiteException:
[19] DB[1] step() [INSERT INTO MyTypes VALUES ('Hello');]DB[1][C] [constraint 
failed]

Is the exception expected?
Is there a better way to add type constraints?

Regards, Mal.

Original issue reported on code.google.com by malcolm....@gmail.com on 29 Aug 2010 at 10:53

[GoogleCodeExporter]

It's not a sqlite4java issue, because the same result happens if you use 
sqlite3 console.

If you do "SELECT typeof('Hello')" from the console, you will get 'text' in 
lowercase. So I think if you rewrite your DDL with "CHECK(typeof(s)='text')", 
it will work.

-- Igor

Original comment by ser...@gmail.com on 29 Aug 2010 at 11:17

• Changed state: WontFix
• Added labels: Type-Other
• Removed labels: Type-Defect