Thread

1. "Cannot insert a duplicate key..." -- but where?

   Will Trillich <will@serensoft.com> — 2003-02-06T22:21:31Z

   in trying to UPDATE a linking-table record (the middle-man in a
   many-to-many relationship) i've encountered an "ERROR:  Cannot
   insert a duplicate key into unique index _relations_p_r_v_ix"
   and can't quite figure it out:
   
   	-- main data table
   	create table person (
   		id serial,
   		lname varchar,
   		primary key ( lname ) -- to keep example simple
   	);
   
   	-- validation table
   	create table relation_v (
   		id serial,
   		name varchar
   	);
   	insert into relation_v(name)values('Friend');
   	insert into relation_v(name)values('Colleague');
   	insert into relation_v(name)values('Family');
   
   	-- linking table (person-to-person, many-to-many)
   	create table relation (
   		person     int4 references person ( id ),
   		relative   int4 references person ( id ),
   		relation_v int4 references relation_v ( id ),
   		primary key (person,relative,relation_v)
   	);
   	...
   
   PERSON is the main DATA TABLE.  RELATION is a LINKING TABLE for
   a many-to-many relationship between PERSON and PERSON.
   RELATION_V is the VALIDATION TABLE containing valid relation
   types. joe can be related to bob as several things: family and
   colleague for example, but since there's no reason to have
   joe-bob-family twice, person/relative/relation_v is the primary
   key.
   
   	SELECT descr
   		FROM
   			relation,
   			person     p,
   			person     r,
   			relation_v t
   		WHERE
   			relation.person     = p.id AND p.lname = <$P_NAME> AND
   			relation.relative   = r.id AND r.lname = <$R_NAME> AND
   			relation.relation_v = t.id AND t.name  = <$R_TYPE>
   
   that sql properly displays ONE RECORD, as it should -- we
   specify each of the three elements of the primary key, in the
   WHERE clause.
   
   so here's the tricky part: i want to update a relation record --
   it's flagged as "family" but it should be "colleague" instead:
   
   	UPDATE relation SET
   		relation_v = (
   			-- get the new value we're looking for
   			SELECT z.id FROM relation_v z WHERE name='Colleague'
   		)
   		FROM
   			person     p,
   			person     r,
   			relation_v t
   		WHERE
   			-- make sure we get the one record to update
   			relation.person     = p.id AND p.lname = <$P_NAME> AND
   			relation.relative   = r.id AND r.lname = <$R_NAME> AND
   			relation.relation_v = t.id AND t.name  = <$R_TYPE>
   
   prodcing ERROR "Cannot insert a duplicate key into unique index"
   
   the WHERE clause is identical to the above select, but given the
   "Cannot insert a duplicate key into unique index" error, it's
   apparently finding more than one record.
   
   (in this test database i've got only three people, and one
   relation between each paid, and for each "direction" a-rel-b and
   b-rel-a for a total of six relation records.
   	person a   relative b    relation_v family
   	person b   relative a    relation_v family
   	person a   relative c    relation_v boss
   	person c   relative a    relation_v employee
   	person b   relative c    relation_v colleague
   	person c   relative b    relation_v family <== should be colleague
   so even if i were to set ALL "relation_v" values to "Family"
   (for example) it should be legal, without hitting the "unique"
   constraint. why the "duplicate key" error?)
   
   the trouble is, i need to use the OLD relation_v.id so i can be
   sure i have the one record i'm looking for, and then the NEW
   relation_v.id to set relation.relation_v properly.
   
   is there a better paradigm for this kind of thing? or is there
   something i'm not grasping about subselects?
   
   -- 
   There are 10 kinds of people:
   ones that get binary, and ones that don't.
    
   will@serensoft.com
   http://sourceforge.net/projects/newbiedoc -- we need your brain!
   http://www.dontUthink.com/ -- your brain needs us!
    
   Looking for a firewall? Do you think smoothwall sucks? You're
   probably right... Try the folks at http://clarkconnect.org/ !
   
   ---------------------------(end of broadcast)---------------------------
   TIP 5: Have you checked our extensive FAQ?
   
   http://www.postgresql.org/users-lounge/docs/faq.html
   
   ----- End forwarded message -----
   
   -- 
   There are 10 kinds of people:
   ones that get binary, and ones that don't.
    
   will@serensoft.com
   http://sourceforge.net/projects/newbiedoc -- we need your brain!
   http://www.dontUthink.com/ -- your brain needs us!
    
   Looking for a firewall? Do you think smoothwall sucks? You're
   probably right... Try the folks at http://clarkconnect.org/ !
   
   

2. Re: "Cannot insert a duplicate key..." -- but where?

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-07T03:35:36Z

   will trillich <will@serensoft.com> writes:
   > in trying to UPDATE a linking-table record (the middle-man in a
   > many-to-many relationship) i've encountered an "ERROR:  Cannot
   > insert a duplicate key into unique index _relations_p_r_v_ix"
   
   I tried to duplicate this, and got:
   
   ...
   regression-# relation.relative   = r.id AND r.lname = 'b' and
   regression-# relation.relation_v = t.id AND t.name  = 'Family';
   UPDATE 1
   regression=#
   
   I think there's something you left out.
   
   Given that you tested the WHERE clause by making an equivalent SELECT,
   I don't believe that the UPDATE is trying to update more than one row.
   It seems more likely that you overlooked a uniqueness conflict against
   some other existing row.
   
   			regards, tom lane
   
   

3. Re: "Cannot insert a duplicate key..." -- SOLVED

   Will Trillich <will@serensoft.com> — 2003-02-07T08:45:50Z

   On Thu, Feb 06, 2003 at 10:35:36PM -0500, Tom Lane wrote:
   > will trillich <will@serensoft.com> writes:
   > > in trying to UPDATE a linking-table record (the middle-man in a
   > > many-to-many relationship) i've encountered an "ERROR:  Cannot
   > > insert a duplicate key into unique index _relations_p_r_v_ix"
   > 
   > I tried to duplicate this, and got:
   > 
   > ...
   > regression-# relation.relative   = r.id AND r.lname = 'b' and
   > regression-# relation.relation_v = t.id AND t.name  = 'Family';
   > UPDATE 1
   > regression=#
   > 
   > I think there's something you left out.
   
   you couldn't be more correct, dang it.
   
   <alert OP=idiot>
   well of course i left something out. i was actually inserting to
   a view, and a rule was inserting in the actual table. turns out,
   i'd forgotten to specify the WHERE id=OLD.id claus in the rule.
   </alert>
   
   <blush>feeling MUCH better now.</blush>
   
   apologies for the bandwidth bloat. sometimes all it takes is
   pressing "send" to reveal one's own density to the world -- and
   to one's self.
   
   -- 
   There are 10 kinds of people:
   ones that get binary, and ones that don't.
    
   will@serensoft.com
   http://sourceforge.net/projects/newbiedoc -- we need your brain!
   http://www.dontUthink.com/ -- your brain needs us!
    
   Looking for a firewall? Do you think smoothwall sucks? You're
   probably right... Try the folks at http://clarkconnect.org/ !