"Cannot insert a duplicate key..." -- but where? - Mailing list pgsql-general

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/ !