ORA-02270 assign a fk to a part of the primary key

0

Hi, I have a problem because a foreign key is part of the primary key of the other table and that is why it gives the error, the model according to the solution of the book is as it is here, so I do not know how do for Oracle to assume it as good.

The problem is only in the "Camino_peregrino" table, and it is because of the foreign key that goes to the table "route"

    Create table CAMINO(
    nombre varchar (20),
    kilmetros_t integer,
    tiempo_t integer,

    constraint pk_camino primary key (nombre)
);

Create table CIUDAD(
    nombre varchar (20),
    comunidad_aut varchar (20),
    codigo_p integer,

    constraint pk_ciudad primary key (nombre)
);

Create table ETAPA(
    nombre_c varchar (20),
    numero varchar(20),
    kilometros_p integer,
    tiempo_p integer,
    ciudad_s varchar (20),
    ciudad_ll varchar (20),

    constraint pk_etapa primary key (nombre_c, numero),
    constraint fk_etapa foreign key (nombre_c) references CAMINO(nombre) on delete cascade,
    constraint fk_etapa1 foreign key (ciudad_s) references CIUDAD(nombre) on delete cascade,
    constraint fk_etapa2 foreign key (ciudad_ll) references CIUDAD(nombre) on delete cascade
);

Create table RECORRIDO(
    nombre_c varchar (20),
    numero varchar (20),
    ciudad varchar (20),

    constraint pk_recorrido primary key (nombre_c, numero, ciudad),
    constraint fk_recorrido foreign key (nombre_c, numero) references ETAPA(nombre_c, numero) on delete cascade,
    constraint fk_recorrido1 foreign key (ciudad) references CIUDAD(nombre) on delete cascade
);

Create table ALBERGUE(
    nombre_a varchar (20),
    capacidad integer,
    precio integer,
    ciudad varchar (20),

    constraint pk_albergue primary key (nombre_a),
    constraint fk_albergue foreign key (ciudad) references CIUDAD(nombre)  on delete cascade
);

Create table PEREGRINO(
    numero_I integer,
    nombre_completo varchar (20),
    direccion varchar (20),

    constraint pk_peregrino primary key (numero_I)
);



Create table CAMINO_PEREGRINO(
    numero_I integer,
    nombre_c varchar (20),
    fecha_paso date,

    constraint pk_camino_peregrino primary key (nombre_c, numero_I, fecha_paso),
    constraint fk_camino_peregrino foreign key (numero_I) references PEREGRINO(numero_I),
    constraint fk_camino_peregrino1 foreign key (nombre_c) references RECORRIDO(nombre_c)
);
    
asked by RicardoBarros 28.10.2017 в 05:01
source

1 answer

0
  

The problem is only in the "Camino_peregrino" table, and it is because of the foreign key that goes to the table "route"

In effect, to define a foreign key, you must point to a primary or single key. nombre_c does not fill those requirements in the PEREGRINO table, so you can not do that.

Now, as I understand the design of your tables (in the future, a little more explanation would be useful), the column nombre_c of the table CAMINO_PEREGRINO represents the name of a path, so I think more logical than the foreign key points to the table CAMINO .

The following foreign key would be right in my opinion:

constraint fk_camino_peregrino1 foreign key (nombre_c) references CAMINO(nombre)
    
answered by 28.10.2017 в 13:58