Enum data type?

(1) By olalonde on 2023-03-16 19:05:05 [link] [source]

Hi, SQLite newbie here. I was wondering if SQLite had plans to support an ENUM data type in the future or if such an addition would be welcome. Thanks.

(2.1) By punkish on 2023-03-16 19:16:45 edited from 2.0 in reply to 1 [link] [source]

you could enum that as follows

sqlite> create table t (a text not null check (a in ('foo', 'bar', 'baz')));
sqlite> insert into t values ('foo');
sqlite> insert into t values ('qux');
Runtime error: CHECK constraint failed: a in ('foo', 'bar', 'baz') (19)
sqlite> insert into t values ('baz');
sqlite> select * from t;
a
---
foo
baz
sqlite>

(3.4) By Keith Medcalf (kmedcalf) on 2023-03-16 19:26:36 edited from 3.3 in reply to 1 [source]

You can already define a valid domain for a column via a check constraint.

create table t
(
   col1 text not null check (col1 in ('value1','value2','value3,'value4'))
);

or by referencing a parent table of allowable values:

create table enum
(
  tid      integer primary key,
  value    text not null unique
);
create table use_enum
(
  tid      integer not null references enum(tid)
);
create index use_enum_tid on use_enum(tid);

or even:

create table enum
(
  value text not null primary key
) without rowid;
create table use_enum
(
  enumvalue text not null references enum(value)
);
create index use_enum__enumvalue on use_enum(enumvalue);

You could even put a check constraint on your enum table if you want to restrict the value to an allowable set of values. You would have to turn on referential integrity enforcement for this to be enforced, however.