Oracle database error message in foreign key

Question

I'm newbie in oracle and trying to create a table with a foreign key, however, an error keeps appearing, and I don't know what is wrong.

This is the little code, and when I try to create "UNIDADESMEDIDA" the error pops up:

create table TIPOUNIDAD(
    ID_TIPOUNIDAD char(20) not null,
    constraint TIPOUNIDAD_PK primary key(ID_TIPOUNIDAD)
);

create table UNIDADESMEDIDA(
    ID_UNIDADMEDIDA integer not null,
    constraint UNIDADESMEDIDA_PK primary key(ID_UNIDADMEDIDA),
    constraint TIPOUNIDAD_FK foreign key(ID_TIPOUNIDAD) references TIPOUNIDAD(ID_TIPOUNIDAD)
);

Error report -
ORA-00904: "ID_TIPOUNIDAD": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Any suggestion?

What do you mean? the second table just have the column "ID_UNIDADMEDIDA", and I'm try to include the "ID_TIPOUNIDAD" as a foreign key — csanchez
– csanchez, Commented Jun 10, 2019 at 20:16

Alex Poole · Accepted Answer · 2019-06-10 20:15:25Z

3

Declaring a foreign key constraint does not automatically create the column; you still need to declare that separately:

create table UNIDADESMEDIDA(
    ID_UNIDADMEDIDA integer not null,
    ID_TIPOUNIDAD char(20),
    constraint UNIDADESMEDIDA_PK primary key(ID_UNIDADMEDIDA),
    constraint TIPOUNIDAD_FK foreign key(ID_TIPOUNIDAD) references TIPOUNIDAD(ID_TIPOUNIDAD)
);

The column name doesn't have to be the same in both tables, and the child column can be nullable even if the parent is not. The data type needs to match though.

Incidentally, there is very rarely a reason to use the char datatype, you should probably change both the parent and child column to varchar2(20).

answered Jun 10, 2019 at 20:15

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle database error message in foreign key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related