Error in primary key in sqlplus?

Question

I am beginner in sql.I am using sqlplus to run the sql query .I used simple query but it shows an error like "MISSING RIGHT PARENTHESIS".My objective is to create the autoincrement primary key .Can anyone solve the error?Thanks in advance...

create table student(rollno int identity(1,1) primary key,
                     name varchar(20),marks int);

That is invalid for Oracle: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAECCFH — a_horse_with_no_name, Mar 09 '18 at 16:26
Which flavor of SQL are you using? "sqlplus" implies Oracle but "identity" is a SQL Server concept. They don't mix. — Stilgar, Mar 09 '18 at 16:26
@Stilgar - Oracle 12c now has an identity concept, but the syntax used by the OP remains SQL Server's version not Oracles. — Andrew, Mar 09 '18 at 16:51
Since when Oracle has `IDENTITY(1, 1)`??? Read the documentation!!! — Eric, Mar 09 '18 at 16:53

score 0 · Answer 1 · answered Mar 09 '18 at 16:30

0

For Oracle, the rollno column could be defined as NUMBER(0010) and primary key. Then you would need to add an ON INSERT trigger to populate rollno from a SEQUENCE. There are many samples of triggers and sequences on this site.

answered Mar 09 '18 at 16:30

Stilgar

451
2
6

score 0 · Answer 2 · edited Mar 09 '18 at 17:11

In oracle 12 you can use a identity column to automatically fill your ID

CREATE TABLE students
(
    "ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999 
    INCREMENT BY 1 START WITH 1 , 
    "NAME" VARCHAR2(20), 
    "MARKS" NUMBER(2,0),
    CONSTRAINT PK_STUDENTS PRIMARY KEY (ID) ENABLE 
);
/

This creates a table without any triggers needed and automatically fills the id column (of not specified with a value) with the next number up to 99999...

If you're using oracle 11 and below, you need a trigger on insert and assign a value (custom_sequence.nextval) to the id column.

CREATE TABLE students
(
    "ID" NUMBER(5,0) not null,
    "NAME" VARCHAR2(20),
    "MARKS" NUMBER(2,0),
    CONSTRAINT PK_STUDENTS PRIMARY KEY (ID) ENABLE 
);
/

CREATE SEQUENCE SEQ_STUDENTS INCREMENT BY 1 START WITH 1;
/
TRIGGER TC_students
before insert on students
for each row
begin
    if (:new.id is null) then
        select SEQ_students.nextval into :new.id from dual;
    end if;
end;
/

And please use VARCHAR2.

Error in primary key in sqlplus?

2 Answers2