MySQL nuances not in Oracle

I use Alan Beaulieu’s book in my entry level SQL class because it is simple and short. The problem is that we focus on Oracle products as an Oracle Academic Partner. Three items that come up frequently are the MySQL ENUM and SET data types, and how to perform multiple row inserts.

MySQL’s ENUM data type

The ENUM data type lets you enter a list of possible string values. It acts like a check constraint in an Oracle database. As such, it restricts what you enter in the column to a value found in the list, or a NULL value provided you’ve not added a not null column constraint.

You could define a table that contains video store item types, like the following:

CREATE TABLE item_type
( id INT
, text ENUM ('VHS','DVD','Blu-ray') );

You should note that the case sensitivity for display is set by how you define them in the ENUM data type when you create the table. They may be entered in mixed, lowercase, or uppercase in an INSERT statement because they’re actually stored as a number. The numbers correlate to their order in an internal list of values, and that list start with the number one.

An alternative syntax with a VARCHAR data type is:

CREATE TABLE item_type
( id INT
, TYPE VARCHAR(20) CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

In MySQL, this syntax is exactly equivalent in behavior to an ENUM data type. The same isn’t true in an Oracle database. While the equivalent check constraint statement is simple, it isn’t alike behavior. The comparable statement for Oracle names the constraint. You can’t name constraints in MySQL.

CREATE TABLE item_type
( id   NUMBER
, TYPE VARCHAR2(20) CONSTRAINT it_type CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

The difference between an ENUM type and a check constraint in MySQL is that the data may not display in a uniform way. MySQL check constraints don’t impose case sensitive validation on input strings, and they also store the data however it is input. Whereas, Oracle does impose case sensitive check constraints and rejects non-conforming strings.

Both databases support single and multiple row INSERT statements. The syntax for single row INSERT statements is very much alike. Multiple row INSERT statement syntax differs between the implementations, as shown later in the blog post.

Oracle Framework to mimic ENUM data type ↓

This framework is predicated on two assumptions. You are migrating from a MySQL to Oracle database, and your external code doesn’t enforce case sensitivity on inputs because it once relied on the MySQL ENUM data type. Oracle check constraints enforce case sensitive inputs, which may be a solution in itself during migration. At least, it is a solution when you handle case sensitive errors gracefully in your external code. Assuming you want an alternative to changing your external application code and you want to avoid throwing errors, here are the steps to do so.

Create testing environment

Create a testing environment that uses a VARCHAR2 column without a database level constraint in conjunction with a database trigger. You can do this because a before insert or update trigger will enforce the equivalent of a database constraint. The trigger lets you define the behavior of the constraint.

Here’s the setup code, which relies on a small item_type lookup table:

Setup code ↓

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

As you may have noticed, the seeded data contains case sensitive strings. The absence of a database-level constraint on a column of a table requires a database trigger. The trigger acts as an alternative to a check constraint and lets you enforce a filtered insertion rule.

Oracle lets you write logic directly into a database trigger, but it is better to write the logic into a stored function or procedure and call it from the trigger’s execution block.

Create a stored function

The stored function takes a string, looks it up in the item_type table, and returns the correct case-sensitive string. The logic is implemented in this function:

CREATE OR REPLACE FUNCTION proper_item_type
( item_type VARCHAR2 ) RETURN VARCHAR2 IS
  CURSOR c (type_name_in VARCHAR2) IS
    SELECT type_name
    FROM   item_type
	  WHERE  UPPER(type_name) = UPPER(type_name_in);
BEGIN
  FOR i IN c (item_type) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

Create a database trigger

After you’ve defined the logic for the trigger, you need to define the database trigger. The following creates a before insert or update database trigger on the item table. The trigger ensures that INSERT statements to the item table store case sensitive copies from the list of possible values found in the item_type lookup table.

CREATE OR REPLACE TRIGGER proper_item_type_t1
  BEFORE INSERT OR UPDATE ON item
  FOR EACH ROW
BEGIN
  :NEW.item_type := proper_item_type(:new_item_type);
END;
/

The trigger takes a non-compliant or compliant string and always return a compliant string before anything is put in the item table.

Test the framework

You can test the framework by inserting an uppercase string BLU-RAY and evaluating the actual results. The following INSERT statement tests the outcome of inserting a non-compliant string.

INSERT INTO item VALUES (item_s1.nextval, 'BLU-RAY', 'Star Wars I', 1001);

You’ll see that a compliant string was actually inserted because the trigger called the function, and the function changed the value from a non-compliant to compliant string. This query let’s you see the result:

SELECT * FROM item;

It inserted Blu-ray in the item_type column.

MySQL’s SET data type

The SET data type lets you enter a list of possible string values but differs from the ENUM data type only in how the values are indexed. Values in the SET are stored as bit values. You may store up to 64 members in a set.

You create a table the same way as you did with ENUM data type, except that you use the SET key word.

CREATE TABLE item_type
( id INT
, TYPE SET ('DVD','VHS','Blu-ray'));

All insert patterns shown for the ENUM data type work with the SET data type.

Multiple row INSERT statements

The most common multiple row insert statement in MySQL is a comma delimited set of parenthetical values. Each parenthetical set of values maps to a row in the INSERT statement.

INSERT INTO item_type
VALUES
( 1, 'DVD' ), ( 2, 'Blu-ray'), ( 3, 'VHS' );

You can’t use the foregoing syntax in Oracle. Oralce only supports a multiple row insert with a subquery.

You can write a subquery in MySQL that fabricates an aggregate table by using numeric and string literals, like the following statement.

INSERT INTO item_type
SELECT 1, 'DVD'
UNION ALL
SELECT 2, 'Blu-ray'
UNION ALL
SELECT 3, 'VHS' ;

Another way to perform multiple row inserts in MySQL is to select from an existing table or filtered result set, like this:

INSERT INTO item_type
SELECT some_int, some_varchar FROM some_table;

You raise an error when you use parentheses in a multiple INSERT statement in MySQL. It’s important to note because the Oracle allows you to write the statement either way.

If you’re coming from MySQL to Oracle, you should note that you may use parentheses to enclose a subquery in Oracle. The Oracle SQL parser works with or without them in an INSERT statement.

The MySQL subquery example is the closest to the Oracle syntax for a multiple row insert. The difference is that Oracle give you the option to enclose a subquery in parentheses when using them inside INSERT statements. Oracle requires that you use the FROM dual clause. I actually wish Oracle would adopt the shorter syntax and maintain backward compatibility to the dual pseudo table.

INSERT INTO item_type
( SELECT 1, 'DVD' FROM dual
  UNION ALL
  SELECT 2, 'Blu-ray' FROM dual
  UNION ALL
  SELECT 3, 'VHS'  FROM dual);

Another way to perform a multiple row insert is to select from an existing table, like this:

INSERT INTO item_type
(SELECT some_int, some_varchar FROM some_table);

Other resources:

You should check Oracle’s document that qualifies differences between MySQL and Oracle. It is the Oracle® Database SQL Developer Supplementary Information for MySQL Migrations.

Written by maclochlainn

February 19th, 2009 at 2:58 am

Posted in MySQL,Oracle,sql

MacLochlainns Weblog

MySQL nuances not in Oracle

Recent Posts

Things Written About

Pages

Blogroll

Archives