I know that this question has been already answered a million of times, but I couldn't find any solution. Well I have these three tables on postgres sql. When I try to create the table incident_has_volunteer it throws the error there is no unique constraint matching given keys for referenced table "incident". I tried to add on the third table

error: there is no unique constraint matching given keys for referenced table “incident”

I know that this question has been already answered a million of times, but I couldn’t find any solution. Well I have these three tables on postgres sql.

CREATE TABLE user_account (
    id SERIAL not null, 
    firstName VARCHAR(60) not null, 
    lastName VARCHAR(60) not null, 
    password VARCHAR(150) not null, 
    email VARCHAR(40) not null UNIQUE, 
    isVolunteer BOOLEAN, 
    complete BOOLEAN, 
    CONSTRAINT pk_user PRIMARY KEY (id));


CREATE TABLE incident (
    id SERIAL not null, 
    patientId INTEGER not null, 
    incidentTime VARCHAR(10) not null, 
    latitude NUMERIC not null, 
    longitude NUMERIC not null, 
    city VARCHAR(60) not null, 
    state VARCHAR(60), 
    country VARCHAR(60), 
    complete BOOLEAN,
    CONSTRAINT pk_incident PRIMARY KEY (id, patientId),
    CONSTRAINT fk_incident FOREIGN KEY (patientId) 
      REFERENCES user_account (id) MATCH SIMPLE 
      ON UPDATE CASCADE ON DELETE CASCADE);


CREATE TABLE incident_has_volunteer (
    incidentId INTEGER not null, 
    volunteerId INTEGER not null, 
    incidentTime VARCHAR(10) not null, 
    complete BOOLEAN, 
    CONSTRAINT pk_incident_has_volunteer PRIMARY KEY (incidentId, volunteerId), 
    CONSTRAINT fk_volunteer FOREIGN KEY (volunteerId)
      REFERENCES user_account (id) MATCH SIMPLE 
      ON UPDATE CASCADE ON DELETE CASCADE, 
    CONSTRAINT fk_incident FOREIGN KEY (incidentId) 
      REFERENCES incident (id) MATCH SIMPLE 
      ON UPDATE CASCADE ON DELETE CASCADE);

​x
 
CREATE TABLE user_account (    id SERIAL not null,     firstName VARCHAR(60) not null,     lastName VARCHAR(60) not null,     password VARCHAR(150) not null,     email VARCHAR(40) not null UNIQUE,     isVolunteer BOOLEAN,     complete BOOLEAN,     CONSTRAINT pk_user PRIMARY KEY (id));​​CREATE TABLE incident (    id SERIAL not null,     patientId INTEGER not null,     incidentTime VARCHAR(10) not null,     latitude NUMERIC not null,     longitude NUMERIC not null,     city VARCHAR(60) not null,     state VARCHAR(60),     country VARCHAR(60),     complete BOOLEAN,    CONSTRAINT pk_incident PRIMARY KEY (id, patientId),    CONSTRAINT fk_incident FOREIGN KEY (patientId)       REFERENCES user_account (id) MATCH SIMPLE       ON UPDATE CASCADE ON DELETE CASCADE);​​CREATE TABLE incident_has_volunteer (    incidentId INTEGER not null,     volunteerId INTEGER not null,     incidentTime VARCHAR(10) not null,     complete BOOLEAN,     CONSTRAINT pk_incident_has_volunteer PRIMARY KEY (incidentId, volunteerId),     CONSTRAINT fk_volunteer FOREIGN KEY (volunteerId)      REFERENCES user_account (id) MATCH SIMPLE       ON UPDATE CASCADE ON DELETE CASCADE,     CONSTRAINT fk_incident FOREIGN KEY (incidentId)       REFERENCES incident (id) MATCH SIMPLE       ON UPDATE CASCADE ON DELETE CASCADE);​

When I try to create the table incident_has_volunteer it throws the error there is no unique constraint matching given keys for referenced table "incident".

I tried to add on the third table and the patientId as a foreign key from table incident table but with no luck. I can’t understand why it throws this error even if I have already set the primary keys on the incident table.

Answer

I’m not an expert in postgres, but I believe that the problem is while fk_incident is referencing incident.id, incident‘s primary key is made of id + patientId. Since incident.id is guaranteed to be unique only in combination with patientId, there’s no way to ensure referential integrity.

I believe that if you add a unique constraint to incident.id (I’m assuming that it would be unique), your foreign key will be legal.

Advertisement

Answer