Cannot insert data into a table - duplicate values

Creating tables and queries

Cannot insert data into a table - duplicate values

Postby Yakhub » Thu Dec 13, 2007 2:29 pm

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   Expand viewCollapse view
[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
Yakhub
 
Posts: 2
Joined: Tue Dec 11, 2007 2:33 pm

Re: Cannot insert data into a table - duplicate values

Postby DrewJensen » Fri Dec 14, 2007 12:51 am

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.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Cannot insert data into a table - duplicate values

Postby Yakhub » Fri Dec 14, 2007 10:05 am

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...
Yakhub
 
Posts: 2
Joined: Tue Dec 11, 2007 2:33 pm

Re: Cannot insert data into a table - duplicate values

Postby kabing » Fri Dec 14, 2007 5:50 pm

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
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests