Problem with unique key
*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=# select * from admin_field_list;
key | field | added_by | add_date | mod_date |
modified_by
-----+------------------+-------------+-------------------------+----------+-------------
1 | Talinga | Greg Peters | 2006-08-28 22:14:53.593 | |
2 | Spring Gully | Greg Peters | 2006-08-28 22:14:53.609 | |
3 | Fairview | Greg Peters | 2006-08-28 22:14:53.609 | |
4 | Argyle | Greg Peters | 2006-08-28 22:14:53.609 | |
5 | Berwyndale South | Greg Peters | 2006-08-28 22:14:53.609 | |
6 | Peat | Greg Peters | 2006-08-28 22:14:53.625 | |
7 | Not Applicable | Greg Peters | 2006-08-28 22:14:53.625 | |
8 | Unknown | Greg Peters | 2006-08-28 22:14:53.625 | |
(8 rows)
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.
* *
* *
* *
* *
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
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
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.