/ sql

ActiveRecord::RecordNotUnique (duplicate key value violates unique constraint). Key already exists.

Recently I was getting an issue while inserting a new record to one of the table in our database. I was getting this specific error -

ActiveRecord::RecordNotUnique (PG::Error: ERROR: duplicate key value violates unique constraint "canonical_cities_pkey"
DETAIL: Key (id)=(*****) already exists.

Understanding the error

The table currently has several thousand entities for canonical cities. But when am trying to insert a new record it builds an canonical_city record and assign it an 'id', say 111, but that 'id' already exist. The primary key which is suppose to auto-increment and generate an unique id is not working properly.

Primary key sequence - responsible for generating an unique primary key for a next record.

On checking, we found primary key sequence was impacted.

What created this weird error

As the table 'canonical_cities' had several thousand records, we copied the data dump from development to our deployment environments - this resulted in primary key sequence setting going wrong. It was set to last value added before we copied the dump.

Solutions

To fix the primary key sequence - we tried the first solution - but there is one more alternative, please check both solution 1 and 2:

First solution
  • Start the rails console
  • Execute couple of lines

connection = ActiveRecord::Base.connection
connection.execute("SELECT setval(pg_get_serial_sequence('canonical_cities', 'id'), MAX(id)) FROM canonical_cities;")

Second solution - this will correct primary key sequence for all tables -

ActiveRecord::Base.connection.tables.each { |t| ActiveRecord::Base.connection.reset_pk_sequence!(t) }

Hope it helps.