Problem of matching unique or primary key for this column-list-CodePudding

I have work to do in SQL, and I have a problem about the referencing of a key. So I have two tables:

CREATE TABLE Surveillances
(
    NumEns NUMERIC(10) REFERENCES Enseignants,
    DateHeureDebut TIMESTAMP(0),
    NumSal NUMERIC(4) REFERENCES Salles,
    PRIMARY KEY(NumEns, DateHeureDebut)
);

CREATE TABLE Horaires
(
    NumEpr NUMERIC(10) REFERENCES Epreuves,
    DateHeureDebut TIMESTAMP(0) REFERENCES Surveillances,
    PRIMARY KEY(NumEpr)
);

I already created the table Surveillances, but when I try to create the table Horaires, this error appears

Error report -

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

I find out that the problem comes from the key DateHeureDebut which is a primary key in the table Surveillances, but not in Horaires.

I tried to modify my Horaires table, but that didn't work:

CREATE TABLE Horaires
(
    NumEpr NUMERIC(10) REFERENCES Epreuves,
    DateHeureDebut TIMESTAMP(0),
    PRIMARY KEY(NumEpr),
    FOREIGN KEY(DateHeureDebut) FROM Surveillances(DateHeureDebut)
);

I just tried this :

CREATE TABLE Horaires
(
    NumEpr NUMERIC(10) REFERENCES Epreuves,
    DateHeureDebut TIMESTAMP(0),
    NumEns NUMERIC(10),
    PRIMARY KEY(NumEpr),
    FOREIGN KEY(NumEns, DateHeureDebut) REFERENCES Surveillances(NumEns, DateHeureDebut)
);

And it works, I mean no error, but in the Horaires table there is no NumEns key.

I have to follow this schema :

The underline words are primary key

CodePudding user response：

To reference a composite primary key you need to match all the columns. Example:

create table table_a (
    num1_n                         number not null,
    num2_n                         number not null,
    PRIMARY KEY(num1_n, num2_n)
)
;


create table table_b (
    table_b_id                     number generated by default on null as identity 
                                   constraint table_b_table_b_id_pk primary key,
    num1_n                         number not null,
    num2_n                         number not null,
    FOREIGN KEY(num1_n, num2_n) 
      REFERENCES table_a(num1_n, num2_n)
)
;

However, the question is - why are you using a composite primary key ? Is it really needed ? It would be a lot easier to use a simple primary key column with no other functionality than primary key and create a unique index on the columns for which the combination is unique. That is just a lot easier to work with. The above example would then be

create table table_a (
    table_a_id                     number generated by default on null as identity 
                                   constraint table_a_table_a_id_pk primary key,
    num1_n                         number not null,
    num2_n                         number not null
)
;

create unique index table_a_u1 ON table_a(num1_n,num2_n);

create table table_b (
    table_b_id                     number generated by default on null as identity 
                                   constraint table_b_table_b_id_pk primary key,
    table_a_id                     number
                                   constraint table_b_table_a_id_fk
                                   references table_a
);

Here is a good SO post on why to use (or not use) composite primary keys.