Ensure single vote per user, per page, in a database

I'm working on a small feature in a Java web application that very closely resembles the vote up/down feature here at stackoverflow.com. I have a database that looks like the following:

VOTE TABLE
id (bi开发者_Go百科gint): surrogate primary key
question_id (bigint):: the question the vote is for
vote_type (int): whether the vote is up or down
user_id (varchar): the username of the person who the vote belongs to

I want to ensure that there is only one vote in the DB per person, per question. What is the best way to enforce this? How would enforce it with the database schema I have described above?

I am currently having problems where a user fires off two vote-up requests and the database then contains 2 'up votes' for that user, when they should only have one vote.

You can enforce is via a Primary Key or Unique index - both concepts are pretty univeresal within databases. Place it on the two columns together, Question_id and User_id. That would only permit 1 entry per user, per question. This will be enforced by the database, even if your application codes lets them vote twice, the database will throw an error on the second record attempting to be inserted. (Even if you are using transactions etc, the database will enforce it correctly.)

If you want to enforce it via database schema, the correct way would be to make (question_id, user_id) an unique key.

Only do the INSERT when user_id and question_id NOT EXISTS e.g.

INSERT INTO TABLE
-- ALL YOUR FIELDS HERE
WHERE NOT EXISTS (
SELECT * FROM TABLE WHERE 
  QUESTION_ID = ? AND USER_ID = ?
)

However you could make user_id and question_id the Primary Key (or another Unique key) so only one record is physically allowed.