Foreign Keys and Primary Keys at the same time

Question

I am trying to create table (orderdetails2); the table has two FKs and a PKs on the two FK columns. Here is my code:

Version 1

    create table OrderDetails2 (
PFOrder_ID Number(3)  FOREIGN KEY REFERENCES Orderr(Order_ID),
PFProduct_ID Number(3) FOREIGN KEY REFERENCES Product(Product_ID),
CONSTRAINT PF PRIMARY KEY (PFOrder_ID,PFProduct_ID),
CONSTRAINT FK_1 FOREIGN KEY (PFProudct_ID)
REFERENCES Product(Product_ID),
CONSTRAINT FK_2 FOREIGN KEY (PFOrder_ID)
REFERENCES Orderr(Order_ID),

Version 2

            create table OrderDetails2 (
    PFOrder_ID Number(3)
    PFProduct_ID Number(3) 
    CONSTRAINT PF PRIMARY KEY (PFOrder_ID,PFProduct_ID),
    CONSTRAINT FK_1 FOREIGN KEY (PFProudct_ID) REFERENCES Product(Product_ID),
    CONSTRAINT FK_2 FOREIGN KEY (PFOrder_ID) REFERENCES Orderr(Order_ID)
);

I am using Oracle express. A problem pops when I run the code. Here it is (for version 1):

ORA-00907: missing right parenthesis

What is the problem?

You have a trailing comma at the end of the last line and a "missing right parenthesis" at the end, to close the parens started in line one in the "create table" statement. — Adam Vandenberg, Dec 26 '10 at 20:35

Jonathan Leffler · Accepted Answer · 2010-12-26T21:01:54.027

Starting from the variant of the code with:

create table OrderDetails2 (
    PFOrder_ID Number(3)
    PFProduct_ID Number(3) 
    CONSTRAINT PF PRIMARY KEY (PFOrder_ID,PFProduct_ID),
    CONSTRAINT FK_1 FOREIGN KEY (PFProudct_ID) REFERENCES Product(Product_ID),
    CONSTRAINT FK_2 FOREIGN KEY (PFOrder_ID) REFERENCES Orderr(Order_ID)
);

You are missing a comma after PFOrder_ID NUMBER(3) and after PFPoduct_ID NUMBER(3).

CREATE TABLE OrderDetails2
(
    PFOrder_ID   NUMBER(3),
    PFProduct_ID NUMBER(3), 
    CONSTRAINT PF   PRIMARY KEY (PFOrder_ID, PFProduct_ID),
    CONSTRAINT FK_1 FOREIGN KEY (PFProduct_ID) REFERENCES Product(Product_ID),
    CONSTRAINT FK_2 FOREIGN KEY (PFOrder_ID)   REFERENCES Orderr(Order_ID)
);

The PRIMARY KEY clause has to be separate from the PFProduct_ID column because it is a compound key. (Is Orderr spelled correctly?) And PFProudct_ID in the original is incorrect. If you're a programmer, you have to be able to spell consistently (even if not correctly in the broader sense of writing).

Also, as a general comment (which I might lift into the answer), the names 'PK', 'FK_1' and 'FK_2' are rather too generic; you'd probably be better off with 'PF_OrderDetails2' and 'FK_1_OrderDetails2' and 'FK_2_OrderDetails2'. — Jonathan Leffler, Dec 26 '10 at 20:56
another case of where consistency is more useful than correctness. cope up with a naming convention for constraints, indices, triggers etc and stick to it. — araqnid, Dec 26 '10 at 20:59

score 2 · Answer 2 · answered Dec 26 '10 at 20:36

2

Change the last comma to a close parenthesis: ).

CREATE TABLE OrderDetails2
(
    ...,
    CONSTRAINT FK_2 FOREIGN KEY (PFOrder_ID) REFERENCES Orderr(Order_ID)
)

answered Dec 26 '10 at 20:36

Mark Byers

811,555
193
1,581
1,452

i updated the code in the question , please take a look again – Bader Dec 26 '10 at 20:39

Foreign Keys and Primary Keys at the same time

Version 1

Version 2

2 Answers2