Foreign key table - oracle sql

Question

Foreign key table - oracle sql

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I have a table in Oracle SQL, and a field, I want it to be a foreign key of the same table, but Oracle gives me an error.

create table Empleado_almacen
(
cod_empleado_almacen int,
dni varchar2(9),
dni_jefe varchar2(9),
nombre varchar2(30),
direccion varchar2(50),
sueldo number(4,1),
telefono number(9),
cod_almacen int,
constraint pk_empleado_almacen primary key(cod_empleado_almacen),

constraint fk_empleado_almacen_jefe foreign key (dni_jefe) 
    references Empleado_almacen(dni_jefe)
        on delete cascade,

constraint fk_almacen_empleado_almacen foreign key (cod_almacen) 
    references Almacen(cod_almacen)
        on delete cascade
);

I do not know if the error is exactly on the foreign key, I think so, I write you the error too:

Informe de error -
Error SQL: ORA-02270: no hay ninguna clave única o primaria correspondiente 
para esta lista de columnas
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
       gives a column-list for which there is no matching unique or primary
       key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
       catalog view

sql oracle oracle-sqldeveloper

asked by alvaro guerrero vallejo 09.06.2017 в 12:03

source

3 answers

Qt handling of QFile files Show text only once

score 2 · Answer 1

The error comes up because the dni_jefe column is not a primary key . I imagine that what you are looking for with that foreign key is to indicate which employee is the current boss, for that you have to use cod_empleado_almacen as reference in the relation.

constraint fk_empleado_almacen_jefe foreign key (dni_jefe) 
references Empleado_almacen(cod_empleado_almacen)
    on delete cascade,

score 0 · Answer 2

0

The error is quite clear, in one of the two tables you want to make the reference FK is not declared as a primary key.

answered by 09.06.2017 в 17:20

score 0 · Answer 3

Thanks to those who have answered, in the end I solved it, I leave the solution here in case someone needs it.

create table Empleado_almacen
(
cod_empleado_almacen int,
dni varchar2(9),
jefe varchar2(2),
nombre varchar2(30),
direccion varchar2(50),
sueldo number(4,1),
telefono number(9),
cod_almacen int,
constraint pk_empleado_almacen primary key(cod_empleado_almacen),

constraint fk_empleado_almacen_jefe foreign key (cod_empleado_almacen) 
    references Empleado_almacen(cod_empleado_almacen)
        on delete cascade,

constraint fk_almacen_empleado_almacen foreign key (cod_almacen) 
    references Almacen(cod_almacen)
        on delete cascade
);