Home > mailing lists

Thread: Problem with unique key

Problem with unique key

From

"Greg Peters"

Date:

28 November 2006, 08:13:19

Hello all,

I have a small problem I hope somebody can help with.

I have a table with the following definition:

CSG=# \d admin_field_list
                                      Table "public.admin_field_list"
   Column    |            Type             |                           Modifiers
-------------+-----------------------------+----------------------------------------------------------------
key         | bigint                      | not null default nextval('admin_field_list_key_seq'::regclass)
field       | character varying(25)       | not null
added_by    | character varying(25)       | not null
add_date    | timestamp without time zone | not null default now()
mod_date    | timestamp without time zone |
modified_by | character varying(25)       |
Indexes:
    "admin_field_list_pkey" PRIMARY KEY, btree ("key")
    "admin_field_list_field_key" UNIQUE, btree (field)

CSG=#

As you can see, I have a primary key called "key", an autoincrementing integer as per the usual. I also have a unique constraint set for the "field" column because I don't want there to be duplicate entries in this column.

This is the data currently in the table:

CSG=#

Now, as you can see, there is no field called "Kogan North", but when I enter the command:

CSG=# insert into admin_field_list (field, added_by) values ('Kogan North', 'Greg Peters');

I get:

ERROR: duplicate key violates unique constraint "admin_field_list_pkey"
CSG=#

I don't understand what is going on. It seems that it can't increment the primary key properly, or for some reason it's trying to assign an incorrect value to the key column. If i change the command to include a value for the key column (in this case the number 9), it seems to work. I've tried dumping/restoring the DB and also tried it on v8.1 and v8.2 with no success. Any ideas?

Kind Regards,

Greg Peters.

Re: Problem with unique key

From

Tomas Vondra

Date:

28 November 2006, 08:30:02

> ERROR:  duplicate key violates unique constraint "admin_field_list_pkey"
> CSG=#
>
> /I don't understand what is going on. It seems that it can't increment
> the primary key properly, or for some reason it's trying to assign an
> incorrect value to the key column. If i change the command to include a
> value for the key column (in this case the number 9), it seems to work.
> I've tried dumping/restoring the DB and also tried it on v8.1 and v8.2
> with no success. Any ideas?/
>
> Kind Regards,
>
> Greg Peters.

The problem is in the primary key constraint, not the unique one. I
guess you have inserted the previous rows with 'key' value specified
directly just like in:

=# insert into admin_field_list (key, field, added_by) values (1,
'Talinga', 'Greg Peters');

Thus the sequence is not set to the correct value and generates values
from 1 ... and these are already in the table. Try this

=# SELECT setval('admin_field_list_key_seq', (SELECT MAX(key) FROM
admin_field_list));

and then the insert again.

Tomas

Re: Problem with unique key

From

Richard Huxton

Date:

28 November 2006, 08:31:24

Greg Peters wrote:
> CSG=# \d admin_field_list
>                                      Table "public.admin_field_list"
>   Column    |            Type             |
> Modifiers
> -------------+-----------------------------+----------------------------------------------------------------
>
> key         | bigint                      | not null default
> nextval('admin_field_list_key_seq'::regclass)

> *Now, as you can see, there is no field called "Kogan North", but when I
> enter the command:*

> CSG=# insert into admin_field_list (field, added_by) values ('Kogan North',
> 'Greg Peters');

> ERROR:  duplicate key violates unique constraint "admin_field_list_pkey"

It's not complaining about "field" but about "key" - look at the
constraint name. I'm guessing you've added rows with manual values for
the "key" column. The sequence doesn't know about these, so is
generating values already in use.

You can use setval(<sequence-name>) to update the sequence's value.

--
   Richard Huxton
   Archonet Ltd

Re: Problem with unique key

From

"Greg Peters"

Date:

28 November 2006, 08:35:35

Thanks all,

with your help I figured out what happened. You are all correct in that the sequence is out of sync with the "key" value. I dumped the db and then selectively restored it from the sql file some time ago by cutting and pasting directly to the command prompt. I must have left out the commands that tell it what the current value of the sequence is. Problen solved.

Many thanks,
Greg.