"duplicate key value violates unique constraint" on updating a record which is part on an M-to-M relationship

[gearoidoceallaigh]

Hi,

This error has bugged me for a while, I've done lots of searching and asked regarding it on Stackoverflow (http://stackoverflow.com/questions/13952050/why-is-datamapper-trying-to-re-save-my-relationships) with no solution forthcoming so said I'd raise it here.

Basically I have a class User defined as follows:

class User    
  include DataMapper::Resource

  has n, :interests, :through => Resource, :constraint => :skip  
end

and a model interests defined as:

class Interest
  include DataMapper::Resource

  has n, :users, :through => Resource, :constraint => :skip
end

This creates a link table with two columns which make up a composite key. I can save new records fine but when I try to update a user like so:

user.update(params[:user]) 

I get the following error:

 duplicate key value violates unique constraint "user_interests_pkey"
 DETAIL:  Key (user_id, interest_id)=(5, 1) already exists.

Would really appreciate anyone who can shed any light on this - its baffled me!

[gearoidoceallaigh]

FWIW this comment from solnic on the dm-rails repo explains the problem:

datamapper/dm-rails#9 (comment)

He suggests avoiding using the autogenerated link tables in datamapper.