ERROR at line 2: ORA-00907: missing right parenthesis

Question

Im getting this error and I need your help:

ERROR at line 2: ORA-00907: missing right parenthesis

CREATE TABLE users(
    user_id int IDENTITY(1,1) PRIMARY KEY,
    user_name varchar2(100) NOT NULL DEFAULT '',
    user_password varchar2(100) NOT NULL DEFAULT '',
    f_name varchar2(50) NOT NULL DEFAULT '',
    l_name varchar2(50) NOT NULL DEFAULT '',
    signature text NOT NULL DEFAULT '',
    link varchar2(255) NOT NULL DEFAULT '',
    category_id int NOT NULL DEFAULT 1
);

Answer 1

NOT NULL DEFAULT ''

'' is an empty string, which is considered as NULL in Oracle. So, makes no sense to have NOT NULL constraint but defaulting the value to NULL again. It is contradicting!

There are multiple issues with your table DDL. Create the table like this:

SQL> CREATE TABLE users(
  2      user_id NUMBER PRIMARY KEY,
  3      user_name VARCHAR2(100) DEFAULT 'default' NOT NULL,
  4      user_password VARCHAR2(100) DEFAULT 'default' NOT NULL,
  5      f_name VARCHAR2(50) DEFAULT 'default' NOT NULL,
  6      l_name VARCHAR2(50) DEFAULT 'default' NOT NULL,
  7      signature VARCHAR2(255) DEFAULT 'default' NOT NULL,
  8      LINK VARCHAR2(255) DEFAULT 'default' NOT NULL,
  9      category_id NUMBER DEFAULT 1  NOT NULL
 10  );

Table created.

SQL>

A note about Identity column. Since you are on version 10g, Identity column is not supported. It was introduced in 12c.

So, you need to explicitly use sequences. You could use the old trigger-sequence approach in your application to populate the primary key. Have a look at the demonstration for example and usage here http://lalitkumarb.wordpress.com/2015/01/20/auto-increment-primary-key-in-pre-12c-releases-identity-functionality/

For example,

TABLE

SQL> CREATE TABLE t (
  2    ID           NUMBER(10)    NOT NULL,
  3    text  VARCHAR2(50)  NOT NULL);
Table created.
SQL>

PRIMARY KEY to be populated by the sequence

SQL> ALTER TABLE t ADD (
  2    CONSTRAINT id_pk PRIMARY KEY (ID));
Table altered.
SQL>

SEQUENCE to support the primary key

SQL> CREATE SEQUENCE t_seq
  2  START WITH 1000
  3  INCREMENT BY 1;
Sequence created.
SQL>

TRIGGER

If you do not want to have the sequence in the INSERT , you could automate it via TRIGGER.

SQL> CREATE OR REPLACE TRIGGER t_trg
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  WHEN (new.id IS NULL)
  5  BEGIN
  6    SELECT t_seq.NEXTVAL
  7    INTO   :new.id
  8    FROM   dual;
  9  END;
 10  /

Trigger created.

SQL>

INSERT

SQL> INSERT INTO t(text) VALUES('auto-increment test 1');

1 row created.

SQL> INSERT INTO t(text) VALUES('auto-increment test 2');

1 row created.

SQL>

Let’s see if we have the ID column auto-incremented with the desired values-

SQL> SELECT * FROM t;

   ID TEXT
----- --------------------------------------------------
 1000 auto-increment test 1
 1001 auto-increment test 2

SQL>

So, the ID column now starts with value 1000 and increments by 1 with subsequent inserts.