How can you use enums in Oracle using SQL only? (No PSQL)
In MySQL that you can do:
CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') );
What will be a similar method of doing this in Oracle?
Reading through a little concerning the MySQL enum, I am speculating the nearest equivalent will be a simple check constraint
CREATE TABLE sizes ( name VARCHAR2(10) CHECK( name IN ('small','medium','large') ) );
but that does not permit you to reference the worthiness through the index. A far more complicated foreign key relationship would be also 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 lengthy while you operate with the view, it might appear that the could replicate the functionality reasonably well.
Now, should you admit PL/SQL solutions, you may create custom object types that may include logic to limit the group of values they are able to hold and also to have techniques to find the IDs and to find the values, etc.
Only at that link you'll find a differentOrworkaround for Oracle, inspired by C language enums: http://www.petefinnigan.com/website/archives/00001246.htm
Shortly put, Pete indicates to define some integer constants and to utilize a SUBTYPE to constrait them:
Red-colored constant number(1):=1
Eco-friendly constant number(1):=2
BLUE constant number(1):=3
YELLOW constant number(1):=4
subtype COLORS is binary_integer range 1..4
Then you're able to declare variables, pass parameters and return values from functions and so forth, with type COLORS.