How to create ENUM type in SQLite?

Question

I need to convert a table from MySQL to SQLite, but I can't figure out how to convert an enum field, because I can't find ENUM type in SQLite.

The aforementioned field is pType in the following table:

CREATE TABLE `prices` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `pName` VARCHAR(100) NOT NULL DEFAULT '',
    `pType` ENUM('M','R','H') NOT NULL DEFAULT 'M',
    `pField` VARCHAR(50) NULL DEFAULT NULL,
    `pFieldExt` VARCHAR(50) NULL DEFAULT NULL,
    `cmp_id` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

I need a field with only three values for the user to chose, and I would like to enforce that in the DB, not just in my application.

Answer 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.