- import Data from Calc table without setting additional primary key (there is already a key column in the Calc table) - In the table definition view set key column as primay key, see: http://www.libreoffice-forum.de/viewtopic.php?p=13914#p13914 --> primary key icon is displayed - save and close - open table definition view again --> primary setting is lost Solution: - allow later primary key setting, even if table contains data - if not possible, offer an error message to the user - allow to define existing column as primary key while importing external data
When you import data from calc and follow the import-wizard you could set the primary-key for the column you wish. Also you could set the right property of the field. When you try to import tables and ignore the wizard (choosing "Create" in the first step of the wizard), the primary-key couldn't be set later in the GUI. This is a bug. You could set the key in SQL with ALTER TABLE "tablename" ADD PRIMARY KEY ("columnname"); Changed the Status to "new", the Importance to "normal" (because there is a possibility to use the whole wizard and change to primary-key there and a possibility in direct SQL)
(In reply to comment #1) > When you import data from calc and follow the import-wizard you could set the > primary-key for the column you wish. No, if I choose the existing column 'ID', I get the error, "There are already following fields set as primary key: ID". I only can choose an additional primary key field, which is worthless. > Also you could set the right property of the field. I don't see any possibility to set field properties in the wizard, I only can set the data type/length. > You could set the key in SQL with > > ALTER TABLE "tablename" ADD PRIMARY KEY ("columnname"); Sorry, I can't see, from where to run such SQL statement, please help. Changed the Importance again to "critical" (because none of the given workarounds seem to work).
I 'm not able to declare all in English. I know, that you have reported something in the german LO-Forum. Try this link: http://de.libreoffice.org/hilfe-kontakt/handbuecher/ Scroll down to the "Base Handbuch". In "Kapitel 7" you find, what you are looking for, when inserting a calc-table in base and choosing the primary-key for it. Its a little bit hidden in the wizard, but it works. In "Kapitel 3" you find where to put SQL-code into a database in the direct way. Way: Tools → SQL or in German: Extras → SQL Let us dicuss the rest in the German LO-Forum. Switch the Importance, when you see, that the primary-key could be set in the two ways I have declared.
(In reply to comment #3) > Scroll down to the "Base Handbuch". In "Kapitel 7" you find, ... Thanks for your help Robert, I've found it. > Switch the Importance, when you see, that the primary-key could be set in the > two ways I have declared. Done! Please also note my 2nd "See also" link.
*** Bug 61547 has been marked as a duplicate of this bug. ***
Lionel: I took a look to: 1) SetPrimaryKey + AdjustFieldDescription in dbaccess/source/ui/tabledesign/TEditControl.cxx 2) appendPrimaryKey in dbaccess/source/ui/tabledesign/TableController.cxx I'm not sure to understand how it works but it seems something is lacking in second one. The more I read this part the more I think there are columns and there are keys (whereas I thought first there were only columns and keys were only property setted on some columns). If I'm right, I don't see the part which adds the primary key to the column in 2) but anyway, I don't have a clue how to add it. Any idea?
Forgot to say, I tried to follow the process and had this: dbaccess/source/ui/tabledesign/TableController.cxx sal_Bool OTableController::doSaveDoc(sal_Bool _bSaveAs) (line 301) calls alterColumns line 420 OTableController::alterColumns() (line 1018) calls appendPrimaryKey line 1344 OTableController::appendPrimaryKey(Reference<XKeysSupplier>& _rxSup,sal_Bool _bNew) it exists of this method on line 785 ("primary key already exists after appending a column") But we haven't done any column appending, we've just altered columns
This bug is still there in Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3 I can confirm this: I copy (^C) a table from Calc (including field names). I paste the clipboard as new table. There is no option to choose an existing field as primary key. The only workaround is to allow the assistent creating an generic ID field as primary key, then edit the database layout, change primary key to wanted field. Save, close, reopen, then delete the generic ID field created by the wizzard. The suggested option to choose the primary key afterwards, seems to work when you enter the design mode (key icon is displayed in the right place), saving and closing gives no error message. But if you try to edit or add any values, you end up with a seemingly write protected table, and most people would never find out, that the yet missing primary key is the reason for this. Suggestion: Please, extend the import GUI to allow selecting one of your imported fields to be the primary key. Please, double check after defining primary key and saving, if this change could actually be commited. Cheers Mike
Created attachment 80778 [details] Shows where to choose the primary key in the import-wizard.
(In reply to comment #8) > This bug is still there in > Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3 > > I can confirm this: > > I copy (^C) a table from Calc (including field names). > I paste the clipboard as new table. > > There is no option to choose an existing field as primary key. In my LO-Version I have the option in the wizard. I have to choose the field, when the wizard will format the field. Click the right button of the mouse over the field. The wizard could be designed better in this position, but that isn't the reported bug. It is reported in https://bugs.freedesktop.org/show_bug.cgi?id=49554 One comment to the Version and the Platform: It has to be the first version the bug appears. I can test it with LO 3.3.4 - the bug exists. Think the bug exists the whole time in LO. If you change the version it means: This is the first version, have a look what has been change from the version before and you will get the wrong code. Also this bug isn't a bug of only FreeBSD or only 64-bit-systems. I will change the settings again.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2741cb8685b28a4454b456b24c1545858fca52e8 fdo#49708 when there is no Primary Key, there is no Primay Key The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to comment #6) > Lionel: I took a look to: > 1) SetPrimaryKey + AdjustFieldDescription in > dbaccess/source/ui/tabledesign/TEditControl.cxx > 2) appendPrimaryKey in dbaccess/source/ui/tabledesign/TableController.cxx Thanks, these code pointers helped me find the source of the problem. (See the commits for the actual problem.) > The more I read this part the more I think there are columns and there are > keys (whereas I thought first there were only columns and keys were only > property setted on some columns). Yes, indeed, there are columns and there are keys (and indexes). Keys (wich are more or less the same as indexes with the "UNIQUE" property set) are basically a *list* columns, it can be one column or several. MS Access has a "Indexed" property for columns, which under the hood creates a one-column index for this column.
The backport of the fix to 4.1 and 4.0 has been submitted to Gerrit.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=002c79346c69c0b7f4953daec7377e71660ae306&h=libreoffice-4-1 fdo#49708 when there is no Primary Key, there is no Primay Key It will be available in LibreOffice 4.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8a6df1a62ded3b6da47022a5b7eec2ba6e81112c&h=libreoffice-4-0 fdo#49708 when there is no Primary Key, there is no Primay Key It will be available in LibreOffice 4.0.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
*** Bug 45251 has been marked as a duplicate of this bug. ***