Bug 117148 - HSQLDB/Firebird allows no edit for table with unique index, but no primary key, contrary to message
Summary: HSQLDB/Firebird allows no edit for table with unique index, but no primary ke...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-04-21 20:33 UTC by Gerhard Weydt
Modified: 2023-01-24 13:06 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
TablkeOnlyUniqueIndex (2.80 KB, application/vnd.sun.xml.base)
2018-04-21 20:34 UTC, Gerhard Weydt 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Gerhard Weydt 2018-04-21 20:33:06 UTC 
When you create a table in Base with Firebird database and save it without having created a primary key, you get the following dialog:

  No primary key

  A unique index or primary key is required for data record identification in this database.
  You can only enter data into this table when one of these two structural conditions has been met.

  Should a primary key be created now?

But, following the alternative stated and creating a unique index only leaves the table not editable. Only if a primary key is added, it becomes editable.

Steps to reproduce (or open attachment TableOnlyUniqueIndex):
1. create a new database document with Firebird database engine
2. create a table using Design View, with some columns
3. save the table; you will get the message shown above
4. reject the creation of a primary key by answering: No
5. choose Tools -> Index design or its symbol
6. create a unique index for the first two columns and save it
7. close the index and table dialogs
8. save the database document
9. open the table for editing

Result: the table is not editable
Expected result: the table should be editable, as stated explicitly in the message shown above.

It doesn't help to close the document and to open it gain, the situation remains unchanged.

To show that editability really depends from a primary key:
10. open the table definition for editing
11. create a new column or select one of the columns not used for the unique index
12: right-click the header of the row describing this column and choose "Primary Key"
13. save the table definition

After that the table can be edited.

Remark: for HSQLDB primary keys were always mandatory, maybe the same test applies now to firebird. But for HSQLDB there is no message that you can use a unique index instead!

This issue is connected with bug 117092, saying (despite its title, because the fact was only discovered after the bug had been created) that it is not possible to create a primary key with more than one column.
In the present state one would have to create
1. a unique index with the columns needed
2. a primary key with one column
where one index could be sufficient.

tested with:
Version: 6.1.0.0.alpha0+
Build ID: 9c4eaa7b81a40d97fe49b85272b40bfeaaf44f86
CPU threads: 4; OS: Windows 10.0; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-04-16_03:31:36
Locale: de-DE (de_DE); Calc: group
Comment 1 Gerhard Weydt 2018-04-21 20:34:56 UTC 
Created attachment 141528 [details]
TablkeOnlyUniqueIndex
Comment 2 Drew Jensen 2018-04-21 22:10:23 UTC 
Just now using the 6.0 dev build the GUI controls work, or in this case do not work, similarly with either hsql or firebird sdbc. 

The GUI controls do not update tables without a primary key.

If IIRC the need for a unique key is for the API (ie. singleselectqueryanalyser service ) and I would suppose that would be true for hsql or firebird. I didn't try that with a macro just now against either engine to check. SQL insert statement in the SQL window works with either engine and I was AR enough to do that just now and in both.

So the GUI stuff, looks to me, to be working as expected but maybe problems lower down?
Comment 3 Robert Großkopf 2018-04-22 07:10:37 UTC 
Seems to be a problem of the GUI-message.

HSQLDB and Firebird could work with tables without primarykey. Only the GUI doesn't allow it. You could input data in a table without primarykey using Tools → SQL.

Create a table "tblName" with only one field "Name". Ignore the warning when saving without PK/Index. Go to Tools → SQL.
Write down
INSERT INTO "tabName" ("Name") VALUES ('BigBoss')
'BigBoss' will inserted in the table, which doesn't has a primarykey and doesn't has a index. Works in HSQLDB and Firebird.

The message is wrong. 
The GUI doesn't support unique index for input of data later.
And: Declaring an index is impossible before saving the table with tableeditor.

I will set this one to NEW.
Comment 4 QA Administrators 2019-06-06 02:54:00 UTC Comment hidden (obsolete)
Comment 5 Alex Thurgood 2019-06-06 08:20:01 UTC 
Still reproducible in

Version: 6.3.0.0.alpha0+
Build ID: dfae42730911256dceb8369528ee9d9944a0fa3e
CPU threads: 8; OS: Mac OS X 10.14.5; UI render: GL; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded
Comment 6 QA Administrators 2021-06-06 05:25:03 UTC Comment hidden (obsolete)
Comment 7 Gerhard Weydt 2021-06-13 19:13:18 UTC 
Still reproducible in
Version: 7.2.0.0.alpha1+ (x64) / LibreOffice Community
Build ID: 93a3e2f86c27b06062708fe788963a0e49f3a90b
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded