SQLPlus中创建自增主键报错：MISSING RIGHT PARENTHESIS 求助

Hey there! Let's work through this issue together. That "MISSING RIGHT PARENTHESIS" error almost always points to a syntax mix-up in your CREATE TABLE statement—especially since you're trying to set up an auto-increment primary key in Oracle (using SQLPlus). Let's break down the correct ways to do this, depending on your Oracle version.

Option 1: Use Identity Columns (Oracle 12c and Later) #

This is the simplest, most modern approach. Oracle 12c introduced identity columns that handle auto-increment natively, no extra sequences or triggers needed. Here's the correct syntax:

CREATE TABLE your_table_name (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- Add your other columns here, e.g.:
    username VARCHAR2(50) NOT NULL,
    join_date DATE DEFAULT SYSDATE
);

A common mistake that triggers the missing parenthesis error is putting the PRIMARY KEY clause before the identity definition (like id NUMBER PRIMARY KEY GENERATED ALWAYS AS IDENTITY). Stick to the order above to avoid syntax issues.

Option 2: Sequence + Trigger (Oracle 11g and Earlier) #

If you're on an older Oracle version, you'll need to use a sequence and trigger to mimic auto-increment behavior. Follow these steps:

1. Create a sequence to generate incrementing values:

CREATE SEQUENCE your_table_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

2. Create your table with a numeric primary key:

CREATE TABLE your_table_name (
    id NUMBER PRIMARY KEY,
    username VARCHAR2(50) NOT NULL,
    join_date DATE DEFAULT SYSDATE
);

3. Create a trigger to automatically populate the primary key with the sequence's next value on insert:

CREATE OR REPLACE TRIGGER your_table_insert_trigger
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    SELECT your_table_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

Common Mistakes to Avoid #

• Misplaced clauses: As mentioned earlier, mixing up the order of IDENTITY and PRIMARY KEY will confuse the parser and throw the missing parenthesis error.
• Unclosed parentheses: Double-check that every opening ( has a matching closing )—especially in column definitions or sequence settings.
• Extra commas: A stray comma at the end of your column list can also cause this error (e.g., username VARCHAR2(50), with no column after it).

Test out the syntax that matches your Oracle version, and you should be able to create your auto-increment primary key without that frustrating error!

内容的提问来源于stack exchange，提问作者VIGNESH R 13MSE0177