alannm37 alannm37 - 1 month ago 10
SQL Question

Can't Create Table in Oracle SQL Developer - Error in Line 1

I am following a tutorial by Derek Banas on YouTube for learning MySQL in One Video. In his tutorial he is using a terminal whereas I am using Oracle MySql Developer, as it's what I use in university and am doing what I can to get ahead in classes, hence why I'm learning from Derek Banas' video! So my problem will be outlined below along with the error and any help/input at all would be greatly appreciated!

In line 8 below, "MEDIUMINT UNSIGNED", Unsigned shows up underlined in red indicating an error. Even when removing this error, it still show's the same error in the output window. I left it in there as it's exactly how Derek Banas does it in his video. (Reference: Derek Banas - MySQL Tutorial)

CREATE TABLE Student(
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(60) NULL,
street VARCHAR(50) NOT NULL,
city VARCHAR(40) NOT NULL,
state CHAR(2) DEFAULT "PA" NOT NULL,
zip MEDIUMINT UNSIGNED NOT NULL,
phone VARCHAR(20) NOT NULL,
birth_date DATE NOT NULL,
sex VARCHAR(1) CHECK (sex IN ('M','F')),
date_entered TIMESTAMP,
lunch_cost FLOAT NULL,
student_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);


The output from this is as follows;

Error starting at line : 1 in command -
CREATE TABLE Student(
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(60) NULL,
street VARCHAR(50) NOT NULL,
city VARCHAR(40) NOT NULL,
state CHAR(2) DEFAULT "PA" NOT NULL,
zip MEDIUMINT UNSIGNED NOT NULL,
phone VARCHAR(20) NOT NULL,
birth_date DATE NOT NULL,
sex VARCHAR(1) CHECK (sex IN ('M','F')),
date_entered TIMESTAMP,
lunch_cost FLOAT NULL,
student_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"
*Cause:
*Action:


No idea what to do from here, spent a while searching around google but found no answer. My guess is it's obviously something to do with Oracle SQL Developer compared to him using the terminal which probably has different formatting rules or something. So yeah as mentioned before, any help appreciated!

Answer

This create table statement comes from mysql and you're executing this on oracle DB.

In oracle this would be:

CREATE TABLE Student(
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
email VARCHAR2(60),
street VARCHAR2(50) NOT NULL,
city VARCHAR2(40) NOT NULL,
state CHAR(2)  NOT NULL, --DEFAULT "PA"
zip number NOT NULL,
phone VARCHAR2(20) NOT NULL,
birth_date DATE NOT NULL,
sex VARCHAR2(1), --CHECK (sex IN ('M','F')),
date_entered TIMESTAMP,
lunch_cost number,
student_id number NOT NULL,
CONSTRAINT "STUDENT_PK" PRIMARY KEY ("STUDENT_ID"),
CONSTRAINT "STUDENT_CHK1" CHECK (sex in ('M','F')) ENABLE);

Auto increment in Oracle is done by:

create sequence studen_seq start with 1 increment by 1;

create or replace trigger student_trg 
before insert on student for each row
declare
begin
:new.student_id := studen_seq.nextval;
end;