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

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

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

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

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

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

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 ?

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.