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------------------------------
Original Message:
Sent: Thu October 20, 2022 11:35 AM
From: Andreas Legner
Subject: Create Type As Enum
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
Original Message:
Sent: Thu October 20, 2022 04:29 AM
From: iliyan iliev
Subject: Create Type As Enum
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
Original Message:
Sent: Thu October 20, 2022 03:17 AM
From: Andreas Legner
Subject: Create Type As Enum
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
Original Message:
Sent: Thu October 20, 2022 02:15 AM
From: iliyan iliev
Subject: Create Type As Enum
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