Search code examples
rubysqliteduplicatessequel

SQLite claims duplicate rows on insert whereas none can be found

I have a table in a SQLite database created with the code below. Note the compound primary key:

db.create_table(:person_hash) do
  Integer :person_id
  Bignum :hash // MD5 hash in hex stored as numeric: hash.to_i(16)
  primary_key [:person_id, :hash]
end

This table has some rows already:

puts db[:person_hash].where(:person_id => 285577).all
# {:person_id=>285577, :hash=>306607097659338192312932577746542919680}

Now, when I try to insert this:

db[:person_hash].insert({:person_id=>285577, :hash=>306607097659338206333361532286405644297})

I get this:

SQLite3::ConstraintException: columns person_id, hash are not unique (Sequel::DatabaseError)

If the row does not already exist in the table, how can it be a duplicate?

I tried inserting another hash for the same person ID instead, and it worked without problems.

Solution

  • This appears to be a bug in SQLite:

    $ sqlite3 SQLite version 3.8.9 OpenBSD Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE person_hash (person_id integer, hash bigint, primary key (person_id, hash)); sqlite> INSERT INTO person_hash VALUES (285577, 306607097659338192312932577746542919680); sqlite> INSERT INTO person_hash VALUES (285577, 306607097659338206333361532286405644297); Error: UNIQUE constraint failed: person_hash.person_id, person_hash.hash