How to use enums in Oracle?
Reading a bit about the MySQL enum, I'm guessing the closest equivalent would be a simple check constraint
CREATE TABLE sizes ( name VARCHAR2(10) CHECK( name IN ('small','medium','large') ));
but that doesn't allow you to reference the value by the index. A more complicated foreign key relationship would also be possible
CREATE TABLE valid_names ( name_id NUMBER PRIMARY KEY, name_str VARCHAR2(10));INSERT INTO valid_sizes VALUES( 1, 'small' );INSERT INTO valid_sizes VALUES( 2, 'medium' );INSERT INTO valid_sizes VALUES( 3, 'large' );CREATE TABLE sizes ( name_id NUMBER REFERENCES valid_names( name_id ));CREATE VIEW vw_sizes AS SELECT a.name_id name, <<other columns from the sizes table>> FROM valid_sizes a, sizes b WHERE a.name_id = b.name_id
As long as you operate through the view, it would seem that your could replicate the functionality reasonably well.
Now, if you admit PL/SQL solutions, you can create custom object types that could include logic to limit the set of values they can hold and to have methods to get the IDs and to get the values, etc.
At this link you can find an alternative solution/workaround for Oracle, inspired by C language enums: http://www.petefinnigan.com/weblog/archives/00001246.htm
Shortly put, Pete suggests to define some integer constants and to use a SUBTYPE to constrait them:
RED constant number(1):=1;GREEN constant number(1):=2;BLUE constant number(1):=3;YELLOW constant number(1):=4;subtype COLORS is binary_integer range 1..4;
After that you can declare variables, pass parameters and return values from functions and so on, with type COLORS.