Keep getting Oracle ORA-00907: missing right parenthesis

Question

I need to create these 2 tables:

CREATE TABLE Volunteer_Assigned_Care_Center
(
    VO_Person_ID    Number(10)   NOT NULL,
    Care_Center_ID  Number(5)   NOT NULL,
    CONSTRAINT CVOPersonID_PK PRIMARY KEY (CVO_Person_ID) References Volunteer (VO_Person_ID),
    CONSTRAINT CareCenterID_FK FOREIGN KEY (Care_Center_ID) References Care_Center(Care_Center_ID)
);

and

CREATE TABLE Volunteer_Supervisor
(
    PH_Person_ID    Number(10)   NOT NULL,
    EM_Person_ID    Number(10)    NOT NULL,
    VO_Person_ID    Number(10)   NOT NULL,
    End_Date    Date    NOT NULL,
    Begin_Date  Date    NOT NULL,
    Hours_Worked    Number(4)   NULL,
    PWork_Unit_ID    Number(4)   NULL,
    CONSTRAINT PHPersonID_FK FOREIGN KEY (PH_Person_ID) References Physician (PH_Person_ID),
    CONSTRAINT EMPersonID_FK FOREIGN KEY (EM_Person_ID) References Employee (EM_Person_ID),
    CONSTRAINT VOPersonID_FK FOREIGN KEY (VO_Person_ID) References Volunteer (VO_Person_ID),
    CONSTRAINT PWorkUnitID_PK PRIMARY KEY (PWork_Unit_ID) References Work_Unit (PWork_Unit_ID)
);

and I am getting this error:

Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

I think this question is duplicated https://stackoverflow.com/questions/24580780/ora-00907-missing-right-parenthesis — Leonidas Menendez, Apr 04 '20 at 06:31

score 3 · Answer 1 · answered Apr 04 '20 at 06:36

Your problem is that the syntax you are using to declare your PRIMARY KEY is FOREIGN KEY syntax. If you want those columns to also be the PRIMARY KEY for the table, you need to declare them as such elsewhere, for example in the column definition:

CREATE TABLE Volunteer_Assigned_Care_Center
(
    VO_Person_ID    Number(10)   NOT NULL PRIMARY KEY,
    Care_Center_ID  Number(5)   NOT NULL,
    CONSTRAINT CVOPersonID_PK FOREIGN KEY (VO_Person_ID) References Volunteer (VO_Person_ID),
    CONSTRAINT CareCenterID_FK FOREIGN KEY (Care_Center_ID) References Care_Center(Care_Center_ID)
);

or with a PRIMARY KEY clause:

CREATE TABLE Volunteer_Supervisor
(
    PH_Person_ID    Number(10)   NOT NULL,
    EM_Person_ID    Number(10)    NOT NULL,
    VO_Person_ID    Number(10)   NOT NULL,
    End_Date    Date    NOT NULL,
    Begin_Date  Date    NOT NULL,
    Hours_Worked    Number(4)   NULL,
    PWork_Unit_ID    Number(4)   NULL,
    PRIMARY KEY (PWork_Unit_ID),
    CONSTRAINT PHPersonID_FK FOREIGN KEY (PH_Person_ID) References Physician (PH_Person_ID),
    CONSTRAINT EMPersonID_FK FOREIGN KEY (EM_Person_ID) References Employee (EM_Person_ID),
    CONSTRAINT VOPersonID_FK FOREIGN KEY (VO_Person_ID) References Volunteer (VO_Person_ID),
    CONSTRAINT PWorkUnitID_PK FOREIGN KEY (PWork_Unit_ID) References Work_Unit (PWork_Unit_ID)
);

Thank you, so I changed it and am STILL getting the same error: — Dominique Connor, Apr 04 '20 at 06:54
@DominiqueConnor it is working fine on [dbfiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9427bddeacf6c784d2dd40cd3b3736b4) — Nick, Apr 04 '20 at 06:55
@DominiqueConnor you didn't change the `PRIMARY` to `FOREIGN` in the foreign key constraint clause - Note **all** the lines starting with `CONSTRAINT` say `FOREIGN KEY` — Nick, Apr 04 '20 at 06:56
Thank you @Nick. It fixed one of my tables. I have to change some names of the other objects to get it to work. Thanks!! — Dominique Connor, Apr 04 '20 at 07:28

Keep getting Oracle ORA-00907: missing right parenthesis

1 Answers1