Bug 140267 - Improve primary key creation when creating/importing a table
Summary: Improve primary key creation when creating/importing a table
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.0.0.3 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsUXEval
Depends on:
Blocks: Base-Enhancements
  Show dependency treegraph
 
Reported: 2021-02-08 16:09 UTC by michael
Modified: 2022-04-06 07:25 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description michael 2021-02-08 16:09:48 UTC
When creating a table in Design view and not setting a column to be the primary key, Base will ask the user whether they want to create a primary key. IIRC the same query also appears when importing data into a new table (and in addition the option to designate a column as the primary key is well hidden). Not sure how the wizard behaves in this situation.

Answering Yes will add a new column named ID, with an auto-increment value and designated as the primary key. This is what Microsoft’s Access does, and what would have made my old database professor cringe.

Suggested improvement: when a user tries to add a new table without a primary key, give them the following options:

- select an existing column to serve as the primary key (or multiple ones for a compound primary key)
- add a new column with an auto-incremented value to serve as the primary key (à la Access)
- proceed without a primary key
Comment 1 Roman Kuznetsov 2022-04-01 17:38:05 UTC
Agree
Comment 2 Heiko Tietze 2022-04-04 07:12:35 UTC
We have two workflows: "Use Wizard to Create Table..." providing various options to set-up the primary key and "Create Table in Design View" with a confirmation box allowing to create another "ID" field (one of the options with the wizard).

I don't get the point with "importing data into a new table".

(In reply to michael from comment #0)
> Suggested improvement: when a user tries to add a new table without a
> primary key, give them the following options...

Users have all options when using the wizard but only a limited yes/no choice for the other workflow. But it's always possible to edit a table and to set another primary key is just a click away (right click at the row header). See also https://help.libreoffice.org/7.3/en-US/text/shared/guide/data_tabledefine.html

Don't see much room for improvement.
Comment 3 Robert Großkopf 2022-04-04 07:45:35 UTC
(In reply to michael from comment #0)
> 
> - select an existing column to serve as the primary key (or multiple ones
> for a compound primary key)

This is already implemented: in Step 3 of the wizard you will see on the left the columns. Now mark one (ore more by holding SHIFT) fields, right mouseclick and set "Primary Key".

> - add a new column with an auto-incremented value to serve as the primary
> key (à la Access)

See the other bug descriptions for this. Importing from Calc doesn't allow to create a new column at all at this moment. Importing with autovalue has never worked, so it has been impossible to create a table with primary key and autovalue for Firebird, because you couldn't change such a field in Firebird to autovalue.

> - proceed without a primary key

This is already implemented. A popup appears when trying to save without primary key, and asks, if a PK should be created. Pressing "No" will save the table without PK. But Base will only read this tables in the GUI. There couldn't be changed data through the GUI, only per direct SQL or macro.
Comment 4 Cor Nouws 2022-04-05 12:16:07 UTC
from comment 3, this looks as a WFM?
Comment 5 Robert Großkopf 2022-04-05 13:20:28 UTC
(In reply to Cor Nouws from comment #4)
> from comment 3, this looks as a WFM?

The only possibility, which isn't implemented, is to create an autoincrement primary key at this moment. But this is bug 130345 or bug 121210.

… but as this moment (LO 7.2 / LO 7.3) the import wizard is mostly unusable (bug 147174).

This bug has been reported for LO 7.0.0.3. So I would say: WFM.