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

Question

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

A foreign key must reference a key in the other table (the whole key). Either the primary key, or a unique constraint. — jarlh, Oct 05 '21 at 08:48
Yes, but in this case, **DateHeureDebut** from **Horaires** references to the primary key **DateHeureDebut** from **Surveillances**, no ? So why it tells me that they are not matching ? — Alonso, Oct 05 '21 at 08:55
`DateHeureDebut` is **not** a primary key in `Surveillances`, it is **part of** the primary key (which consists of `NumEns` **and** `DateHeureDebut`). The foreign key constraint has to reference the entire key, not just part of the key. — Mark Rotteveel, Oct 05 '21 at 08:57
Ok, I think I understand what you meant, is this correct : ```FOREIGN KEY(DateHeureDebut) REFERENCES Surveillances(NumEns, DateHeureDebut)``` — Alonso, Oct 05 '21 at 09:04
Ok, I just add a column `NumEns` in my `Horaires` table, the referencing work, but the table `Horaires` does not contain the key `NumEns`. — Alonso, Oct 05 '21 at 09:11

score 1 · Accepted Answer · answered Oct 05 '21 at 09:20

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.

I don't know actually why I'm using a composite primary key, by the way I just learned about what is a composite primary key. But I have a schema which show us the structure of the project, I add it in the question — Alonso, Oct 05 '21 at 09:37
My answer already has an example of how to reference a composite primary key - you should be able to figure out the syntax for your project. — Koen Lostrie, Oct 05 '21 at 11:00

score 0 · Answer 2 · answered Oct 06 '21 at 11:49

I see that you have accepted the other answer. So maybe you don't need any more help on this. After all, your request is not only about how to technically introduce a foreign key constraint in the database, but on where to use them to guarantee data consistency. If you don't need help here, imply ignore this post :-)

I am not even able yet to give a complete answer. But what I have to say is much too long for a comment...

Your data model is about exams (epreuves). An exam can take place in several rooms (salles) and this relation is stored in the table occupations. An exam takes place at a certain date/time. Instead of simply storing the date/time in the exams table, you have created a separate table horaires for this on the same key (the exam number). The data model would be simpler, did you store the date/time in the exams table epreuves and drop the schedule table horaires.

Let's look at three sample exams, "MATH 1" and "PHYSICS 2" and "ARTS 4", all taking place on October 27, 2021. "MATH 1" takes place at 10:00 am in rooms 1 and 2, and "PHYSICS 2" at 10:00 pm in rooms 2 and 3, and "ARTS 4" takes place at 3:00 pm in rooms 2 and 3. That means that at 10 am in room 2 there are students of math and physics exams, probably placed such that a math student sits next to a physics student, so they cannot copy their neighbor's results :-)

Now there is another table "surveillances" saying which teacher (enseignants) is in which room on the exam date/times. This table allows more than one teacher per room and date/time. Maybe for instance a math teacher and a physics teacher both at 10 am in room 2. (I suppose this is only about exam date/times, not about regular classes, but I may be wrong.)

The problem with this: You want the tables to match. There must not be an exam in a room without a surveillance teacher (and a surveillance without an exam taking place may also be not desired).

Now, first of all, please confirm that my explanation matches what you want your data model to represent. I will then give you my opinion on how to go about constraints there.

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

2 Answers2