Add ENum Attribute to existing table using Alter

Question

I am using oracle XE and trying to add a attribute that restricts to only three available value 'Small','Medium','large' to an existing table using Alter Table and Enum.

Tried doing,

ALTER TABLE TRUCK ADD TTYPE ENUM('SMALL','MEDIUM','LARGE');

but gets invalid option

ALTER TABLE TRUCK ADD TTYPE ENUM('SMALL','MEDIUM','LARGE');
 *

where the error highlights after ENUM.

I think I am having syntax error. Please help to resolve.

Answer 1

I think you want this:

ALTER TABLE TRUCK 
 ADD (ttype VARCHAR2(6) 
 CONSTRAINT con_type 
 CHECK (ttype in('SMALL','MEDIUM','LARGE'))
 );

Answer 2

There is no ENUM datatype in Oracle. You can use a foreign key constraint to a reference table (with 3 rows) or a CHECK constraint instead:

ALTER TABLE truck 
 ADD ttype VARCHAR(6),
 ADD CONSTRAINT ttype_check
 CHECK (ttype IN ('SMALL','MEDIUM','LARGE')) ;

Answer 3

Oracle Database 23ai added enumeration domains. These are a series of name/value pairs. Create this first, for example:

create domain truck_types as enum (
 small = 'S', medium = 'M', large = 'L'
);

You can then use this as the data type for a column.

create table truck ( truck_id int );
alter table truck add ttype truck_types;

This has an implicit check constraint meaning you can only store the enum values (S, M, L) in ttype.

You can use the enum names in SQL statements. This returns the corresponding value. You can converted values stored in an enum column to names by passing the column to domain_display:

insert into truck 
values ( 1, truck_types.small ),
 ( 2, truck_types.medium ),
 ( 3, 'L');
select truck_id, ttype, domain_display ( ttype ) 
from truck;
 TRUCK_ID TT DOMAIN
---------- -- ------
 1 S SMALL 
 2 M MEDIUM
 3 L LARGE