ENUM and types creation in SQLite

Question

I have to convert the code creating a database in Postgres to SQLite. I'm stuck because SQLite does not allow to make enums or create types. I'm seeking a way to get around this problem.

For example, imagine we have in Postgres :

CREATE TYPE AckStatusEnum AS ENUM (
'ACKNOWLEDGED','NOT_ACKNOWLEDGED','ERROR_RECEIVED','NOT_SENT'
);

CREATE TABLE ACK_EVENT(
...
    ACK_STATUS AckStatusEnum,
...
);

CREATE TYPE aggr_type AS (
...
    severity alertseverityenum ,
...
);

The "..." represents other rows. How to translate that to SQLite? How to create an ENUM type, which could be used into a table AND into an other type? Besides how to simulate this type creation (which should also be usable into a table and a type)?

Answer 1

SQLite doesn't support defining datatypes. Indeed, SQLite doesn't even enforce datatypes to columns.

If you really need to limit values, I recommend using FOREIGN KEY as this:

CREATE TABLE AckStatusEnum (id INTEGER PRIMARY KEY AUTOINCREMENT, AckStatusEnum TEXT);
INSERT INTO AckStatusEnum(AckStatusEnum) VALUES('ACKNOWLEDGED'), ('NOT_ACKNOWLEDGED'), ('ERROR_RECEIVED'), ('NOT_SENT');

CREATE TABLE ACK_EVENT(
    ...
    ACK_STATUS REFERENCES AckStatusEnum(id),
    ...
);