Creating ENUM and types in SQLite

Question

I need to convert the code creating the database in Postgres for SQLite. I am stuck because SQLite does not allow you to do enumerations or create types. I am looking for a way around this problem.

For example, imagine what 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 , ... ); 

"..." represents other lines. How to translate this to SQLite? How to create an ENUM type that can be used in an AND table in another type? Besides simulating the creation of this type (which should also be used in the table and type)?

Answer 1

SQLite does not support the definition of data types. Indeed, SQLite does not even apply data types to columns.

If you really need to limit the values, I recommend using a FOREIGN KEY like 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), ... );