Reputation: 3415
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)
);
Upvotes: 2
Views: 2496
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
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