Create Type As Enum
iliyan iliev asked whether Informix can create an ENUM type like PostgreSQL. Andreas Legner said no such SQL-level concept exists and suggested a CHECK constraint with an IN list. Art Kagel added that for numeric enum-like behaviour one can use a SMALLINT column with a lookup table and foreign key, or a conversion function.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I see in keyword ENUM. Anyone try to create type(not row type) as enum ?! i don't find any example with enum or how to use enum in informix. ------------------------------ iliyan iliev developer MoI-DCIS ------------------------------ #Informix
You mean an SQL level user defined data type (UDT) as an ENUM? So a predefined limited set of named values to be used in certain places? I don't think this concept exists on SQL level. ------------------------------ Andreas Legner ------------------------------
This concept have in postgresql. PostgreSQL: Working with ENUM types – SQL Bits (sql-bits.com) do you have in informix ?! ------------------------------ iliyan iliev developer MoI-DCIS ------------------------------
Ahh, you need this for a table column's values?
In Informix you'd do this with a check constraint on the column:
create table dairy_stocks
(
product varchar(50),
stock int,
check (product in ("milk", "butter", "cheese"))
);HTH,
Andreas
------------------------------
Andreas Legner
------------------------------
Using a CHECK constraint is similar to an enum, but it would be a set of strings where an enum in "C" replaces the names with small numeric values. If I needed enum-like behavior, I would use a SMALLINT column and populate it using a function that took in the names and returned a number. The alternative would be a lookup table that does the translation and populate the column by looking up the number.
So, if a table is like this:
CREATE TABLE mytab(
keycol INT,enum_col SMALLINT
);
ALTER TABLE mytab ADD CONSTRAINT FOREIGN KEY( enum_val )
REFERENCES enum_tab( enum_val )CONSTRAINT mytab_fk1;
So, an insert would either look like this:
INSERT INTO mytab( keycol, enum_val ) values (123, enum_func( 'fred' ) );or this:
INSERT INTO mytab( keycol, enum_val )
SELECT 123, enum_val
FROM enum_tab
WHERE enum_str = 'fred';------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------