How I Made Sure You Can't Like and Dislike the Same Post at Once

Quick one today. While building the reactions feature for our team's forum project (Go + SQLite), I hit a question that sounds obvious until you actually have to implement it: what happens when a user likes a post, then clicks dislike on the same post? Do they end up with both a like and a dislike registered? Because that would be a bug — a post shouldn't be simultaneously liked and disliked by the same person.

The naive approach (don't do this)

The tempting first instinct is: every click on Like or Dislike just inserts a new row into a reactions table.

sql
INSERT INTO reactions (user_id, target_type, target_id, value)
VALUES (?, ?, ?, ?);

This breaks immediately. Click like, then dislike, and now you've got two rows for the same user and the same post — one with value = 1, one with value = -1. Your like/dislike counts are now both incremented, which is exactly the bug we're trying to avoid.

The fix: let the database enforce the rule

Instead of trying to handle this entirely in application code, I put a constraint directly on the table:

sql
CREATE TABLE reactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
target_type TEXT NOT NULL CHECK (target_type IN ('post','comment')),
target_id INTEGER NOT NULL,
value INTEGER NOT NULL CHECK (value IN (1,-1)),
UNIQUE (user_id, target_type, target_id)
);

That UNIQUE (user_id, target_type, target_id) line is doing the real work. It tells SQLite: this combination can only exist once. No matter what your application code does, the database itself won't let a user have two reaction rows for the same post.

Now the application logic gets simple

With that constraint in place, "toggle a reaction" becomes a clean three-way decision:

go
func SetReaction(db *sql.DB, userID, targetID int64, targetType string, value int) error {
var existing int
err := db.QueryRow(
SELECT value FROM reactions WHERE user_id = ? AND target_type = ? AND target_id = ?,
userID, targetType, targetID,
).Scan(&existing)

switch {
case err == sql.ErrNoRows:
    // no reaction yet — insert
    _, err = db.Exec(`INSERT INTO reactions (user_id, target_type, target_id, value) VALUES (?, ?, ?, ?)`,
        userID, targetType, targetID, value)
    return err

case existing == value:
    // clicked the same button again — remove it (toggle off)
    _, err = db.Exec(`DELETE FROM reactions WHERE user_id = ? AND target_type = ? AND target_id = ?`,
        userID, targetType, targetID)
    return err

default:
    // switching like <-> dislike — update in place
    _, err = db.Exec(`UPDATE reactions SET value = ? WHERE user_id = ? AND target_type = ? AND target_id = ?`,
        value, userID, targetType, targetID)
    return err
}

}

Three outcomes, mapped directly to what a user actually expects:

First click → your reaction gets recorded.
Click the same button again → your reaction gets removed (un-like / un-dislike).
Click the other button → your reaction flips.
Proving it actually works

The satisfying part was writing a test that walks through all three states in sequence and checks the counts after each step:

go
repo.ToggleReaction(userID, "post", postID, Like)
// likes = 1, dislikes = 0

repo.ToggleReaction(userID, "post", postID, Like) // same button again
// likes = 0, dislikes = 0 (toggled off)

repo.ToggleReaction(userID, "post", postID, Like)
repo.ToggleReaction(userID, "post", postID, Dislike) // switch
// likes = 0, dislikes = 1

Seeing that last assertion pass — dislikes went up exactly as likes went down, never both — was a nice moment. It's a small feature, but it's the kind of thing that's genuinely broken in a lot of quickly-built apps, and a single UNIQUE constraint is all it took to make it structurally impossible to get wrong.

Anyone else have a case where a database constraint saved you from writing a pile of application-level validation code?