Auto-increment column: differences in SQL syntax between Oracle and MySQL

Question

I am a university student and need to submit a coursework using iSQL* Plus by Oracle.

I am trying to create a table with the following SQL Statement:

    CREATE  TABLE  Category 
( `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR (45) NULL ,
  PRIMARY KEY (`id`) );

This results in the following message:

ORA-00911: invalid character

It's referring to the tick ` sign. So I tried the following, using a single quote instead:

    CREATE  TABLE  Category 
( 'id' INT(11) NOT NULL AUTO_INCREMENT ,
  'title' VARCHAR (45) NULL ,
  PRIMARY KEY ('id') );

The error:

ORA-00904: : invalid identifier

So one more try with " - The Error:

( "id" INT(11) NOT NULL AUTO_INCREMENT ,
          *

ORA-00907: missing right parenthesis

If I remove the (11) behind the INT it will complaint about the AUTO_INCREMENT attribute.

    CREATE  TABLE  Category 
( "id" INT NOT NULL AUTO_INCREMENT ,
  "title" VARCHAR (45) NULL ,
  PRIMARY KEY ("id") );

I thought SQL is SQL and there are not really differences on these very basic levels. I thought that things are getting different on deeper levels?

• how I get my statement working?
• what would you recommend for someone familiar with MySQL to learn Oracle?

Answer 1

Not all SQL is the same. Neither Oracle nor MySQL support the actual SQL standard of IDENTITY.

Oracle does not use backticks... you don't actually need to quote your identifiers. Better not to so you don't end up inadvertently using an invalid character in an identifier.

Oracle numerics are called NUMBER, and can take an optional precision and scale.

CREATE TABLE Category
(
  id    NUMBER(11)   NOT NULL,
  title VARCHAR2(45) NULL,
  PRIMARY KEY (id)
)

To do an AUTO_INCREMENT, create a sequence:

CREATE SEQUENCE seq_category_id START WITH 1 INCREMENT BY 1;

Then when you insert into the table, do this:

INSERT INTO category
VALUES (seq_category_id.nextval, 'some title');

To do this automatically, like AUTO_INCREMENT, use a before insert trigger:

-- Automatically create the incremented ID for every row:
CREATE OR REPLACE trigger bi_category_id
BEFORE INSERT ON category
FOR EACH ROW
BEGIN
    SELECT seq_category_id.nextval INTO :new.id FROM dual;
END;

Or:

-- Allow the user to pass in an ID to be used instead
CREATE OR REPLACE TRIGGER bi_category_id
BEFORE INSERT ON category
FOR EACH ROW
DECLARE
    v_max_cur_id NUMBER;
    v_current_seq NUMBER;
BEGIN
    IF :new.id IS NULL THEN
        SELECT seq_category_id.nextval INTO :new.id FROM dual;
    ELSE
        SELECT greatest(nvl(max(id),0), :new.id) INTO v_max_cur_id FROM category;
        SELECT seq_category_id.nextval INTO v_current_seq FROM dual;
        WHILE v_current_seq < v_max_cur_id
        LOOP
            SELECT seq_category_id.nextval INTO v_current_seq FROM dual;
        END LOOP;
    END IF;
END;

Now, as far as discovering these differences, you can often just search for something like "oracle identity" or "oracle auto_increment" to see how Oracle does this.

Answer 2

Oracle doesn't have auto increment columns. You need to create a sequence and before insert trigger that reads NEXTVAL from the sequence and sets the value for the column