Auto Enlarge Column: SQL Syntax Differences Between Oracle and MySQL

Question

I am a university student and I need to provide coursework using iSQL * Plus from Oracle.

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

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

As a result, the following message appears:

ORA-00911: invalid character

This refers to the tick sign. So I tried the following, using one quote instead:

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

Error:

ORA-00904 :: invalid identifier

So, one more attempt with "- Error:

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

ORA-00907: missing right parentheses

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

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

I thought SQL was SQL, and there really are no differences at these basic levels. I thought that everything is changing at deeper levels?

• How can I make my expression work?
• What would you recommend to someone familiar with MySQL to learn Oracle?

Answer 1

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

Oracle does not use backlinks ... in fact, you do not need to specify your identifiers. It is better not to make sure that you do not accidentally use an invalid character in the identifier.

Oracle numbers are called NUMBER and can take extra precision and scale.

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

To do AUTO_INCREMENT, create a sequence:

 CREATE SEQUENCE seq_category_id START WITH 1 INCREMENT BY 1; 

Then, when you insert into the table, do the following:

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

To do this automatically, for example AUTO_INCREMENT, use the 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, if you find these differences, you can simply simply look for something like “oracle identity” or “oracle auto_increment” to see how Oracle does it.

Answer 2

There are no auto-increment columns in Oracle. You need to create a sequence and before insert trigger that reads NEXTVAL from the sequence and sets the value for the column