Getting missing parenthesis in my sql code

Question

Using ORACLE SQL Developer

CREATE TABLE PAYMENT (
    Payment_ID VARCHAR2(300) NOT NULL,
    Account_ID VARCHAR(300) NOT NULL,
    Discount_Code VARCHAR(50),
    Gift_Card VARCHAR(50),
    Cust_Rewards INTEGER, 
    Credit_Card VARCHAR(50),
    Checking VARCHAR(50),
    Bank_Account VARCHAR(80) NOT NULL,
    Store_Card VARCHAR(50),
    CONSTRAINT PK_PAYMENT_ID PRIMARY KEY REFERENCES PAYMENT(Payment_ID),
    CONSTRAINT FK_PAYMENT_Account_ID FOREIGN KEY (Account_ID) REFERENCES ACCOUNT(Account_ID)
);

I get an error report that states "ORA-00906: missing left parenthesis:

How can I fix this??

EDIT: Full code being ran:

CREATE TABLE CUSTOMER (
    Customer_ID VARCHAR(300) NOT NULL,
    Cust_FName VARCHAR(20) NOT NULL,
    Cust_LName VARCHAR(20) NOT NULL,
    Cust_Email VARCHAR(50),
    Cust_Address VARCHAR(100),
    
    CONSTRAINT PK_CUSTOMER PRIMARY KEY (Customer_ID)
);

/*Account table*/
CREATE TABLE ACCOUNT (
    Account_ID VARCHAR(300) NOT NULL,
    Customer_ID VARCHAR(300) NOT NULL,
    Cust_Username VARCHAR(50) NOT NULL,
    Cust_Password VARCHAR(50) NOT NULL,
    Pet_Name VARCHAR(25),
    Pet_DOB INTEGER,
    Cust_Rewards INTEGER,
    
    CONSTRAINT PK_ACCOUNT PRIMARY KEY (Account_ID,Customer_ID),
    CONSTRAINT FK_ACCOUNT FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER(Customer_ID)
);

CREATE TABLE PAYMENT (
    Payment_ID VARCHAR2(300) NOT NULL,
    Account_ID VARCHAR(300) NOT NULL,
    Discount_Code VARCHAR(50),
    Gift_Card VARCHAR(50),
    Cust_Rewards INTEGER, 
    Credit_Card VARCHAR(50),
    Checking VARCHAR(50),
    Bank_Account VARCHAR(80) NOT NULL,
    Store_Card VARCHAR(50),
    CONSTRAINT PK_PAYMENT_ID PRIMARY KEY REFERENCES PAYMENT(Payment_ID),
    CONSTRAINT FK_PAYMENT_Account_ID FOREIGN KEY (Account_ID) REFERENCES ACCOUNT(Account_ID)
);

Error starting at line : 32 in command-

CREATE TABLE PAYMENT (
    Payment_ID VARCHAR2(300) NOT NULL,
    Account_ID VARCHAR(300) NOT NULL,
    Discount_Code VARCHAR(50),
    Gift_Card VARCHAR(50),
    Cust_Rewards INTEGER, 
    Credit_Card VARCHAR(50),
    Checking VARCHAR(50),
    Bank_Account VARCHAR(80) NOT NULL,
    Store_Card VARCHAR(50),
    CONSTRAINT PK_PAYMENT_ID PRIMARY KEY REFERENCES PAYMENT(Payment_ID),
    CONSTRAINT FK_PAYMENT_Account_ID FOREIGN KEY (Account_ID) REFERENCES ACCOUNT(Account_ID)
)

Error report-

ORA-00906: missing left parentesis 00906. 0000 - "missing left parenthesis" *Cause: *Action:

Is that the **complete, exact** error message? I'm guessing not, since it contains a : that indicates there's more after it If so, then [edit] your post and add that message. It's on the screen right in front of you, but we can't see that screen from here. Also, is this the ONLY code that is being executed? If not, add the rest of your code. — Ken White, Oct 12 '22 at 02:18
If you use SQLPlus, it will give a little marker indicating where the error is (or at least where the parser thinks it is) — Gary Myers, Oct 12 '22 at 02:22
Ken White, I have edited the post with all needed info. Let me know if you need anything else! Thank you for your help! — MStew_7627, Oct 12 '22 at 03:14
1. Remove everything from the table definition that fails. 2. Add a column/constraint/another option to the table definition and execute `create table` statement. 3. Drop a newly created table. 4. Repeat steps 2, 3 until you get an error. 5. Edit your question or post another one if it is still required. This is called **debugging**. — astentx, Oct 12 '22 at 10:09

score 1 · Answer 1 · answered Oct 12 '22 at 03:36

A foreign key must reference the primary key of the parent table - the entire primary key. (SQL. How to reference a composite primary key Oracle?)

here is the fiddle http://sqlfiddle.com/#!4/eacfa48

CREATE TABLE PAYMENT (
    Payment_ID VARCHAR2(300) NOT NULL ,
   Account_ID VARCHAR(300) NOT NULL,
   Customer_ID VARCHAR(300) NOT NULL,
    Discount_Code VARCHAR(50),
    Gift_Card VARCHAR(50),
    Cust_Rewards INTEGER, 
    Credit_Card VARCHAR(50),
    Checking VARCHAR(50),
  Bank_Account VARCHAR(80) NOT NULL,
    Store_Card VARCHAR(50),
  CONSTRAINT PK_PAYMENT_ID PRIMARY KEY (Payment_ID),
  CONSTRAINT FK_PAYMENT FOREIGN KEY (Account_ID, Customer_ID) REFERENCES ACCOUNT(Account_ID, Customer_ID)
);

one of the reasons I never use composite primary keys.

Getting missing parenthesis in my sql code

1 Answers1