lamp2lapo

The MySQL enum column type is useful for enforcing constraints on your text data columns. We’ll look at how to emulate this functionality in Oracle using standard SQL constraints. As an example, let’s look at a simple MySQL comments table.

CREATE TABLE comments(
comment_text text,
comment_type ENUM ('spam', 'approved')
)

MySQL will ensure that your comment_type field only contains one of those two values. I do want to warn you that in typical MySQL fashion, only a warning is issued when you try to insert a value that is not allowed and the field value is set to the empty string. How this is allowed or acceptable behaviour baffles me.


mysql> insert into comments values('hi', 'ham');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from comments;
+--------------+--------------+
| comment_text | comment_type |
+--------------+--------------+
| hi | |
+--------------+--------------+
1 row in set (0.00 sec)


To create a true enum in Oracle, we simply make the comment_type column varchar2 and add a constraint to it.

CREATE TABLE comments(
comment_text varchar2(4000),
comment_type varchar2(8),
CONSTRAINT cons_comment_type
CHECK (comment_type IN ('spam', 'approved'))
)

Now Oracle won’t let you insert values into the comment_type field that aren’t in the approved list, the expected behaviour of an enum.

SQL> insert into comments values ('hi', 'ham');
insert into comments values ('hi', 'ham')
*
ERROR at line 1: ORA-02290: check constraint (ORABLOG.CONS_COMMENT_TYPE) violated

Oracle once again comes through at helping you ensure data integrity. The MySQL way lets you limit values to the ones you choose AND the empty string. Yet another reason to make the switch to Oracle today!

Note that this article came out of my effort to port the Trackback Validator Plugin to Orablog to reduce the amount of spam trackbacks we receive! There is no license attached to the plugin so I’m checking with the authors to see if I can post my changes as part of the Orablog distribution.