What Is Enum Data Type in Oracle?


Angela Bailey

What Is Enum Data Type in Oracle?

In Oracle, the ENUM data type is not natively supported. However, you can simulate an enumeration-like behavior using a combination of check constraints and user-defined types. This allows you to define a predefined set of values that a column can store.

Creating an Enum-Like Data Type

To create an enum-like data type in Oracle, you need to follow these steps:

  1. Create a new object type using the CREATE TYPE statement.
  2. Add the desired values as attributes to the object type.
  3. Create a table with a column of the newly created object type.
  4. Add a check constraint to enforce the predefined values for the column.

Let’s walk through an example to understand this better:

Step 1: Create Object Type

To create an object type, you can use the following syntax:

   value VARCHAR2(20)

The above code creates an object type called enum_type. It has a single attribute called value, which will store the allowed values for our enum-like data type.

Step 2: Add Values as Attributes

In this step, we will add our desired values as attributes to the object type. For example, let’s say we want our enum-like data type to have three possible values: ‘ACTIVE’, ‘INACTIVE’, and ‘PENDING’.


The above code adds the attributes ACTIVE, INACTIVE, and PENDING to the enum_type.

Step 3: Create Table with Enum-Like Column

In this step, we will create a table that has a column of our newly created enum-like data type.

CREATE TABLE my_table (
   status enum_type

The above code creates a table called my_table. It has a single column called status, which is of type enum_type.

Step 4: Add Check Constraint for Valid Values

To enforce the predefined values for the column, we need to add a check constraint.

ALTER TABLE my_table
ADD CONSTRAINT valid_status_values
CHECK (status.value IN ('ACTIVE', 'INACTIVE', 'PENDING'));

The above code adds a check constraint called valid_status_values. It ensures that only the allowed values (‘ACTIVE’, ‘INACTIVE’, and ‘PENDING’) can be inserted into the status column of the my_table.

In Conclusion

In Oracle, although there is no direct support for ENUM data type, you can simulate its behavior using user-defined types and check constraints. By following the steps outlined in this tutorial, you can create enum-like data types and ensure that only the predefined values are stored in the corresponding columns.

Remember: Enum-like data types can provide clarity and maintainability to your database schema by restricting the possible values for specific columns.

Now that you understand how to create an enum-like data type in Oracle, you can apply this knowledge to structure your database schema more effectively!