How to create ENUM type in SQLite?

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:

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.

3 Answers

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.

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.

8

To expand on MPelletier’s answer, you can create the tables like so:

CREATE TABLE Price ( PriceId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name VARCHAR(100) NOT NULL, Type CHAR(1) NOT NULL DEFAULT ('M') REFERENCES PriceType(Type) ); CREATE TABLE PriceType ( Type CHAR(1) PRIMARY KEY NOT NULL, Seq INTEGER ); INSERT INTO PriceType(Type, Seq) VALUES ('M',1); INSERT INTO PriceType(Type, Seq) VALUES ('R',2); INSERT INTO PriceType(Type, Seq) VALUES ('H',3);

Now the enumeration values are available directly in the Price table as they would be using an ENUM: you don’t need to join to the PriceType table to get the Type values, you only need to use it if you want to determine the sequence of the ENUMs.

Foreign key constraints were introduced in SQLite version 3.6.19.

7

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobGtxaWeDeHvHqK5mrJ9isLOxwK2cZp2eqrpuwNipnGahnmLAsrjIrZw%3D

 Share!