How can I fix this PostgreSQL foreign key error?

Question

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".

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

Key (candidate)=(6) is not present in table "candidates" - clear message. Either wrong candidates id or missing candidate. — Sebastian Brosch
– Sebastian Brosch, Commented Mar 6, 2019 at 15:44
Your foreign key constraint relates vote.candidate to candidates.id. There is no row in candidates where id = 6. — MatBailie
– MatBailie, Commented Mar 6, 2019 at 15:46

Gordon Linoff · Accepted Answer · 2019-03-06 15:42:30Z

3

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.

answered Mar 6, 2019 at 15:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-06 15:47:52Z

1

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

answered Mar 6, 2019 at 15:47

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Collectives™ on Stack Overflow

How can I fix this PostgreSQL foreign key error?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related