SQL Foreign Key invalid identifier

Question

I want to create these two tables in apex.oracle but the foreign key doesn't work. I get this error:

ORA-00904: "CNP": invalid identifier.

Can you please tell what is the problem? Thanks

CREATE TABLE CERCETATOR
(
    CNP CHAR(13) CONSTRAINT PK_CERCETATOR PRIMARY KEY,
    VARSTA NUMBER(2),
    TELEFON NUMBER(12),
    EXPERIENTA NUMBER(2)
)

CREATE TABLE ECHIPAMENT
(
    COD_ECH NUMBER(4) CONSTRAINT PK_ECH PRIMARY KEY,
    MARIME VARCHAR2(2),
    CULOARE VARCHAR2(10),
    MATERIAL VARCHAR2(20),
    PRET NUMBER(3,2),

    CONSTRAINT FK_CERCETATOR 
        FOREIGN KEY (CNP) REFERENCES CERCETATOR(CNP)
)

Do you see a column called `CNP` in the definition of `ECHIPAMENT`? Because I don't. And neither does the Oracle compiler, which is why it throws the ORA-00904 error. — APC, Jan 05 '18 at 21:30
By the way, `CERCETATOR.CNP` should almost certainly be `VARCHAR2(13)` not `CHAR(13)`. `VARCHAR2` is the standard string type in Oracle. `CHAR` is provided for portability and ANSI compatibility but is [rarely what you actually need](https://stackoverflow.com/a/42165653/230471). — William Robertson, Jan 06 '18 at 10:02

iosappdevguy · Answer 1 · 2018-01-06T13:23:14.137

-1

This is your issue.

CONSTRAINT FK_CERCETATOR FOREIGN KEY (CNP) REFERENCES CERCETATOR(CNP)

in this statement, when you are creating FOREIGN KEY (CNP) there is no CNP in ECHIPAYMENT.

edited Jan 06 '18 at 13:23

answered Jan 05 '18 at 21:46

iosappdevguy

55
1
7

1

There isn’t a field with the right data type; so the CNP column needs to be added, rather one of the existing columns being used fo the constraint. – Alex Poole Jan 05 '18 at 23:56
Tables have columns, not fields. – William Robertson Jan 06 '18 at 09:59
I am not here to debate proper usage of word column vs field...here is Wikipedia for you. “The word 'field' is normally used interchangeably with 'column'.” – iosappdevguy Jan 06 '18 at 13:05

SQL Foreign Key invalid identifier

1 Answers1