CODEWITHSUNDEEP
sqlpostgresql
Jama Mohamed
Jama Mohamed

Reputation: 3415

How can I fix this PostgreSQL foreign key error?

I have created three tables (users, candidates and votes), and when a user create a vote, it is sent to the votes table which has a foreign key candidate from the candidates table, but when I create a new vote, I get this error

ERROR:  insert or update on table "votes" violates foreign key constraint "votes_candidate_fkey"
DETAIL:  Key (candidate)=(6) is not present in table "candidates".

enter image description here

The candidate table has the candidate with id 6, but when I create a vote I get a foreign key error, how can I solve this, below is the shema

  CREATE TABLE IF NOT EXISTS users(
    id serial PRIMARY KEY,
    first_name VARCHAR (100) NOT NULL,
    last_name VARCHAR (100) NOT NULL,
    other_name VARCHAR (100) NOT NULL,
    email VARCHAR (100) UNIQUE NOT NULL,
    password VARCHAR (100) NOT NULL,
    phone_Number VARCHAR (100) UNIQUE NOT NULL,
    passport_Url VARCHAR (255) NOT NULL,
    is_Admin BOOLEAN DEFAULT 'no'
  );

  CREATE TABLE IF NOT EXISTS candidates(
    id serial PRIMARY KEY,
    office INTEGER REFERENCES offices(id),
    party INTEGER REFERENCES parties(id),
    candidate INTEGER UNIQUE REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS votes(
    created_by INTEGER REFERENCES users(id),
    office INTEGER REFERENCES offices(id),
    candidate INTEGER REFERENCES candidates(id),
    created_On DATE NOT NULL DEFAULT NOW(),
    PRIMARY KEY (office, created_By)
  );

Upvotes: 2

Views: 2496

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32031

The message is very much clear that your candidates tabled does not contain id=6 but you are trying to insert that value into votes tables that's why you got the error, cause it is a foreign key violation

in votes table candidate INTEGER REFERENCES candidates(id) this is relate to candidates tables id column not with candidate column that you thought

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

User 6 exists, but you only have five candidates and their ids are 1, 2, 3, 4, and 5.

Your foreign key is to id, not the candidate column:

candidate INTEGER REFERENCES candidates(id),
----------------------------------------^

The appropriate id is 5, for user 6.

You may want to set up the candidates table with the primary key being the user id (that is, candidates are subsets of users). If so remove the serial column:

CREATE TABLE IF NOT EXISTS candidates(
  candidate INTEGER PRIMARY KEY REFERENCES users(id),
  office INTEGER REFERENCES offices(id),
  party INTEGER REFERENCES parties(id)
);

I would recommend this. If you as the database designer are already confused about the difference between a "candidate" and a "user", then no doubt future users of the database will have the same confusion.

Upvotes: 3

Related Questions