(Error) ORA-02270: no matching unique or primary key for this column-list

I have the code below and I’m running it step by step:

CREATE TABLE registration (
    constraint pkks primary key (cod_class, number_rgm),
    cod_class          NUMBER(8),
    number_rgm         NUMBER(8),
    dt_registration    DATE
);

CREATE TABLE class (
    constraint fk_class foreign key (cod_class) references registration (cod_class),
    cod_class      NUMBER(8),
    number_schoolyear   NUMBER(4),
    number_series       NUMBER(2),
    sg_class            VARCHAR2(2),
    cod_school          NUMBER(6),
    cod_grade           NUMBER(2),
    cod_period          NUMBER(2)
);

CREATE TABLE alumn (
    constraint fk_rgm foreign key (number_rgm) references registration (number_rgm),
    number_rgm         NUMBER(8),
    name_alumn         VARCHAR2(40),
    name_father        VARCHAR2(40),
    name_mother        VARCHAR2(40),
    birth              DATE,
    id_sex             CHAR(1)
);

But when I create the second and third tables I get the following error:

ORA-02270: no matching unique or primary key for this column-list

How do I solve this error and create the tables in this logic? I am saving all primary keys in the registration table and the other tables will only reference it.

Answer

You’ve got your foreign keys backwards. The cod_class and number_rgm columns in registration should be foreign keys referencing the class and alumn tables respectively.

CREATE TABLE class (
    constraint pk_class primary key (cod_class),
    cod_class      NUMBER(8),
    number_schoolyear   NUMBER(4),
    number_series       NUMBER(2),
    sg_class            VARCHAR2(2),
    cod_school          NUMBER(6),
    cod_grade           NUMBER(2),
    cod_period          NUMBER(2)
);

CREATE TABLE alumn (
    constraint pk_rgm primary key (number_rgm),
    number_rgm         NUMBER(8),
    name_alumn         VARCHAR2(40),
    name_father        VARCHAR2(40),
    name_mother        VARCHAR2(40),
    birth              DATE,
    id_sex             CHAR(1)
);

CREATE TABLE registration (
    constraint pkks primary key (cod_class, number_rgm),
    constraint fk_class foreign key (cod_class) references class(cod_class),
    constraint fk_rgm foreign key (number_rgm) references alum(number_rgm),
    cod_class          NUMBER(8),
    number_rgm         NUMBER(8),
    dt_registration    DATE
);