Bug 117148 - FIREBIRD allows no edit for table with unique index, but no primary key, contrary to message
Summary: FIREBIRD allows no edit for table with unique index, but no primary key, cont...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
Reported: 2018-04-21 20:33 UTC by Gerhard Weydt
Modified: 2019-06-06 08:20 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

TablkeOnlyUniqueIndex (2.80 KB, application/vnd.sun.xml.base)
2018-04-21 20:34 UTC, Gerhard Weydt

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:
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]
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

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