Appending data to Base Table fails

[BobGlen]

I feel really stupid, I have followed the posts on OOO and here on Libre Office but no matter what, I cannot get an append of additional columns to succeed.

Existing table (SalesHistorySep15) has a primary key (not auto key)
ItemNo is primary key and has several other columns
SalesJan13 to SalesSep15
I now want to add column SalesOct15.
I open a calc file and connect to database (F4) and drag and drop table into calc sheet.
Create a new column SalesOct15 and Populate with data.

Have tried several Methods. Here below is one. I can document the other methods and the different errors, if its a real problem, at this point I suspect I have missed something basic and the fault lies with my me.

1. Create new column in Base called SalesOct15.
2. Copy ItemNo (Primary Key) and SalesOct15 in Calc. Paste into table in Base.
3. Choose Append Data and Use 1st Row as column names.
4. Move SalesOct15 up to second position to match position on Source
   Receive following Error.
   Violation of Unique Contraint SYS_PK_165: duplicate value(s) for Column(s) “Item No” in statement {INSERT INTO “SalesHistorySep15” (“ItemNo”,SalesOCT15") VALUES (??)} Continue anyway …Yes No.

Have a great day

Bob

LIBREOffice Version: 5.0.2.2 (x64)
Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe
Java 8 Update 60 (64 bit)
Windows 10 (64 bit)

[peterwt]

When you attempt to copy from Calc to the Table in Base it will try to create new records in the Base table with the Columns ItemNo and SalesOct15. Since the Table already has the ItemNo and it is the Primary Key (which does not allow duplicates) you get the error “duplicate values” where SYS_PK_ is the Primary Key Index for ItemNo.

Why not just create the new Column SalesOct15 in the Base Table and then open the Table and enter the data for SalesOct15 directly into the Table. I can see no reason to copy the Table to Calc, create the new Column, and copy back to Base.

[SteveR]

@bobglen: The following goes beyond your question. You need to examine your database structure. 1) You should not need a field such as “SalesOct15”. 2) I have found it easier and Base complains less if you have an auto-increment primary key. 3) Use Base as a front-end, use another (open-source) database as the back-end.