CODEWITHSUNDEEP
ruby-on-rails
Peter R
Peter R

Reputation: 3516

RoR/Postgres - Error with two column unique index. "duplicate key value violates unique constraint", even without any records

So Contract can have many Services. and the join table is ContractsService(contracts_services), pretty simple.

Index set with:

def change
  add_index :contracts_services, [:contract_id, :service_id], unique: true
end

If I try to add like this (or via the API, or in any way, so not specific to this piece of code):

ContractsService.where(contract_id: 16, service_id: 17).first_or_create!

I get this error: 

ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_contracts_services_on_contract_id_and_service_id"
DETAIL:  Key (contract_id, service_id)=(16, 17) already exists.

However:

irb(main):015:0> ContractsService.where(contract_id: 16, service_id: 17).count
D, [2017-10-26T07:25:01.782432 #4] DEBUG -- :    (1.7ms)  SELECT COUNT(*) FROM "contracts_services" WHERE "contracts_services"."deleted_at" IS NULL AND "contracts_services"."contract_id" = $1 AND "contracts_services"."service_id" = $2  [["contract_id", 16], ["service_id", 17]]
=> 0

I'm stuck here, never seen this before. I'm I missing something simple? or is the index corrupt? Something else?

Upvotes: 2

Views: 699

Answers (1)

Peter Balaban
Peter Balaban

Reputation: 643

Seems like you have default_scope in ContractsService model. Look at the SQL: "contracts_services"."deleted_at" IS NULL

And your unique index doesn't include deleted_at column.

If you will try ContractsService.rewhere(contract_id: 16, service_id: 17).count then you should find this record

Upvotes: 3

Related Questions