Adding type constraints to tables

[Former user]

Originally reported on Google Code with ID 20 ``` 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. ```

Reported by `malcolm.gorman` on 2010-08-29 10:53:44

[Igor Sereda]

``` 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 ```

Reported by `sereda` on 2010-08-29 11:17:22 - Status changed: `WontFix` - Labels added: Type-Other - Labels removed: Type-Defect