CODEWITHSUNDEEP
postgresql
user2276280
user2276280

Reputation: 601

There is no unique constraint matching given keys for referenced table

I have a postgresql table:

    CREATE TABLE Appointments(
        DateAndTime timestamp,
        RoomNumber integer,
    PRIMARY KEY(DateAndTime, RoomNumber));

And am trying to create the following table with fields that reference the first

    CREATE TABLE AttendsAppointment(
        DateAndTime timestamp REFERENCES Appointments(DateAndTime),
        RoomNumber integer REFERENCES Appointments(RoomNumber),
    PRIMARY KEY(DateAndTime, RoomNumber));

This is throwing the error listed in the title but I can't figure out why. Anyone know?

Upvotes: 2

Views: 7558

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

As the message says: There is no unique key matching the given key. You want to reference DateAndTime of Appointments, but it is not unique.

A foreign key references a unique key (usually the primary key) of another table. So there could be a table DateAndTimes holding all DateAndTime values, and Appointments and AttendsAppointment could reference that table.

Or AttendsAppointment could reference Appointment, but then you would build a foreign key on both fields. Both columns together are unique in table Appointments.

CREATE TABLE AttendsAppointment
(
  DateAndTime timestamp,
  RoomNumber integer,
  PRIMARY KEY(DateAndTime, RoomNumber),
  FOREIGN KEY(DateAndTime, RoomNumber) REFERENCES Appointments(DateAndTime, RoomNumber)
);

Upvotes: 4

Related Questions