database - How to use enums in Oracle?

Question

How do you use enums in Oracle using SQL only? (No PSQL)

In MySQL you can do:

CREATE TABLE sizes (
   name ENUM('small', 'medium', 'large')
);

What would be a similar way to do this in Oracle?

question from:https://stackoverflow.com/questions/203469/how-to-use-enums-in-oracle

Answer 1

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.