0

Hello, I want to create tables in database (using SQL) and add foreign keys to each table refering to the other one. For example:

CREATE TABLE ONE(
    ID          INT             NOT NULL,
    NAME        VARCHAR(20)     NOT NULL,
    TWOID       INT             NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (TWOID) REFERENCES TWO (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE TWO(
    ID          INT             NOT NULL,
    NAME        VARCHAR(20)     NOT NULL,
    ONEID       INT             NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (ONEID) REFERENCES ONE (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

When I run this query, it will give me the following error "missing right parenthesis", I tried to add CONSTRAINT fk1 FOREIGN KEY.... but it didn't change anything ...
I also tried to remove the ON DELETE CASCADE ON UPDATE CASCADE, the error message changed to "table or view does not exist", which logically is correct since I am calling the table "TWO" which is not created yet but will be created later.
How to fix both problems: "missing right parenthesis" and "table or view does not exist"
Thank you

Edited by pritaeas: Moved.

2
Contributors
8
Replies
18
Views
4 Years
Discussion Span
Last Post by pritaeas
0

I'm using Oracle, no only this simple example, if it works everything will work.

Edited by cmps

0

the error after I run the query is:

SQL> @test.sql
    FOREIGN KEY (TWOID) REFERENCES TWO (ID) ON DELETE CASCADE ON UPDATE CASCADE
                                                              *
ERROR at line 6:
ORA-00907: missing right parenthesis


    FOREIGN KEY (ONEID) REFERENCES ONE (ID) ON DELETE CASCADE ON UPDATE CASCADE
                                                              *
ERROR at line 6:
ORA-00907: missing right parenthesis
0

Isn't NAME a reserved word, try [NAME] instead.

Update:
See this. "...this is not available in Oracle Database...". Remove ON UPDATE CASCADE

Edited by pritaeas

0

Aha, this is weird because I have a sample project where they used oracle as database and the queries has ON UPDATE CASCADE in it :/
Anw, thank you pritaeas

0

Okay this part was solved, now the error is:

SQL> @test.sql
    FOREIGN KEY (TWOID) REFERENCES TWO (ID) ON DELETE CASCADE
                                   *
ERROR at line 6:
ORA-00942: table or view does not exist


    FOREIGN KEY (ONEID) REFERENCES ONE (ID) ON DELETE CASCADE
                                   *
ERROR at line 6:
ORA-00942: table or view does not exist

Logically this is correct .. but how to fix it ?
I think it can be solved with ALTER, but is there any other way ?

1

That's the problem with a circular foreign key. I'm not sure if Oracle has an option to disable foreign key checks temporarily, but that's what I'd search for.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.