Oracle APEX创建表遇ORA-00904错误：CNP标识符无效求助

Hey there! Let's break down why you're hitting that error with your foreign key setup. The issue is super straightforward:

• You’re trying to create a foreign key using CNP on the ECHIPAMENT table, but you never defined the CNP column in that table! Oracle can’t find a column named CNP in ECHIPAMENT, so it throws the "invalid identifier" error.

Foreign keys work by linking a column in your child table (here, ECHIPAMENT) to the primary key of a parent table (CERCETATOR.CNP). For this to work, the child table must first have that column defined, and its data type has to match the parent table’s primary key exactly.

Corrected SQL Statements #

Here’s the fixed version of your code, where we add the CNP column to ECHIPAMENT (matching the CHAR(13) type from CERCETATOR):

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),
    CNP CHAR(13), -- Added this column to link to the parent table's primary key
    CONSTRAINT FK_CERCETATOR FOREIGN KEY (CNP) REFERENCES CERCETATOR(CNP)
);

Quick Tips #

• Always ensure the foreign key column’s data type and length match the parent table’s primary key. In this case, CERCETATOR.CNP is CHAR(13), so ECHIPAMENT.CNP must also be CHAR(13) (not VARCHAR2 or a different length).
• When creating dependent tables in Oracle APEX, create the parent table first (like CERCETATOR) before the child table (ECHIPAMENT)—your original code does this correctly, but it’s a good habit to remember if you ever run these statements separately.

内容的提问来源于stack exchange，提问作者Toma Rares