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

Question

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);

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 1

Very simply - one table of primary key acts as a foreign key for another table, so you must ensure that both key is referenced or not.

Simply you will not assign foreign key to the column of another table which does not have primary key. this is called as RDBMS.

Thanks