How to create ENUM type in SQLite?

Solution 1:

SQLite way is to use a CHECK constraint.

Some examples:

CREATE TABLE prices (
 id         INTEGER                                PRIMARY KEY,
 pName      TEXT CHECK( LENGTH(pName) <= 100 )     NOT NULL DEFAULT '',
 pType      TEXT CHECK( pType IN ('M','R','H') )   NOT NULL DEFAULT 'M',
 pField     TEXT CHECK( LENGTH(pField) <= 50 )     NULL DEFAULT NULL,
 pFieldExt  TEXT CHECK( LENGTH(pFieldExt) <= 50 )  NULL DEFAULT NULL,
 cmp_id     INTEGER                                NOT NULL DEFAULT '0'
)

This will limit the pType column to just the values M, R, and H, just like enum("M", "R", "H") would do in some other SQL engines.

Solution 2:

There is no enum type in SQLite, only the following:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

Source: http://www.sqlite.org/datatype3.html

I'm afraid a small, custom enum table will be required in your case.