Page 1 of 1
Cannot insert data into a table - duplicate values
Posted: Thu Dec 13, 2007 2:29 pm
by Yakhub
Hello.
In my base there is a table "Companies", with "ID" column inside (of course, there are other columns too, but it doesn't matters now).
The "ID" column is set as "INTEGER", PRIMARY KEY and "Auto-increment"
Now, the problem:
I'm trying to insert data into this table.
I typed in the firs record - everything is OK. It received ID = 0. But, when I try to insert a secound column, I receiving an error:
Code: Select all
[b]Violation of unique constraint SYS_PK_53: duplicate value(s) for column(s) "ID" [/b]
What am I doing wrong? What do I have to change, if I want to use this table?
feel free to correct my english
Re: Cannot insert data into a table - duplicate values
Posted: Fri Dec 14, 2007 12:51 am
by DrewJensen
Well, it sounds as if your table really is not set to have auto_increment for the ID column.
if this is an embedded database ( in other words you just ran the New Database wizard and used all defaults ) then the most likely mistake is human - I say this because I have done it more times then I will admit to here...What happens when I create that ID column and go to set the Auto_Increment value to TRUE I inadvertantly set the Required = TRUE instead ( I have done it a bunch ) and the UI is designed, IMO, in a way to make that easy. Then I go to enter data and voila, just the same problem.
Second - there was a release - maybe it was one of the developer builds I think where the setting was just not taking...but I don't think that ever was an issue with a 'Final Release' version.
Third - you are connecting to a different datasource type ( dBase, MySQL, MS Access ) in these cases there are additional steps you need to take to use auto_increment - so let us know here.
OK - So, open that table again in edit mode ( right click on the table name and select edit ) and double check that column for auto_increment - my guess is that it will not be set. If that is the case then you can do this.
Close the table design editor.
Open the SQL window ( Tools > SQL )
In the SQL window enter the SQL command to make that column of type IDENTITY. So if you table where named Table1, and the column is named ID, you would enter this command:
ALTER TABLE "Table1" ALTER COLUMN "ID" IDENTITY
Click on Execute
That should fix your problem.
Re: Cannot insert data into a table - duplicate values
Posted: Fri Dec 14, 2007 10:05 am
by Yakhub
DrewJensen wrote:Well, it sounds as if your table really is not set to have auto_increment for the ID column.
if this is an embedded database ( in other words you just ran the New Database wizard and used all defaults ) then the most likely mistake is human - I say this because I have done it more times then I will admit to here...What happens when I create that ID column and go to set the Auto_Increment value to TRUE I inadvertantly set the Required = TRUE instead ( I have done it a bunch ) and the UI is designed, IMO, in a way to make that easy. Then I go to enter data and voila, just the same problem.
Yes, this DB is embedded.
I'm not sure, what was wrong - i just removed all table relations, deleted this table and created it again - and now everything seems to be OK...
Re: Cannot insert data into a table - duplicate values
Posted: Fri Dec 14, 2007 5:50 pm
by kabing
I had this problem once, after I had deleted some records from a table. I'm not sure if that was related or not.
The issue was the "Identity" one mentioned by DrewJensen. But I did not have to use the SQL Tool to fix it. I opened the table in Edit mode, and in the "Auto-increment Statement" box typed IDENTITY. That fixed it. See
this thread of mine in the OOoforum.org Base forum.
kabing