Bug 108057 - Primary key should default to AutoValue=Yes when type is integer
Summary: Primary key should default to AutoValue=Yes when type is integer
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Database-Tables
  Show dependency treegraph
Reported: 2017-05-24 13:34 UTC by Howard Johnson
Modified: 2021-09-10 07:53 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Screen shot (34.08 KB, image/png)
2017-05-24 13:34 UTC, Howard Johnson

Note You need to log in before you can comment on or make changes to this bug.
Description Howard Johnson 2017-05-24 13:34:35 UTC
Created attachment 133516 [details]
Screen shot

(Preface: Now that I have Base doing some of the hard things I had hoped it would do, I plan to submit a number of simple bug/improvement reports and this is the first.  My hope is to take some of the rough edges off for new Base users.  There are some little things that could easily be improved I think.  I'm going to start with the most basic things that could be improved, that unnecessarily make Base hard to use for the new user.)

1) When creating a new database, and selecting either HSQLDB 1.8 or FireBird, and then when a new table is created in design mode, and an ID field is created as the first field, and set to Integer, and then set as the Primary Key, I think the AutoValue should be automatically set to Yes.

Steps to create this:

Open LibreOffice and from the menu select:
        Create Table In Design View...
          Set Field Name to "ID" | Set Field Type to "Integer"
          Right click on green arrow left of ID and select "Primary Key"

          Result below is AutoValue remains set to "No".

I think AutoValue should be Yes.

While an experienced person might want to insert their own primary key values, the new user almost surely will want to just have the ID come up working.

But even as an experienced user with 40 years of database experience, I find that having to set this to Yes, each and every time I create a simple database for testing purposes is just frustrating and one more unnecessarily click.  

We've been building computers to help do our work for us.  I think this is something the software can do to save a step almost all of the time.  

It is rare indeed that I have a table that I don't use an AutoValue ID on these days.  In the over 100 tables I now use with base I can only think of 2 or 3 that I use where the primary key is not AutoValue.  So yes, it's possible to have a valid database with AutoValue off.  The one I can think of right now is a small table I have of months, where the ID is the month number.  But in all of the other 100+ tables I have the ID is set to AutoValue=Yes.

Thanks for your consideration of this.
Comment 1 Alex Thurgood 2017-05-26 06:35:36 UTC
@Howard : the table creation wizard does this for you with hsqldb, if you don't assign a primary key when setting up your table, then it will ask whether you want to create one or use an existing field. In either case, there is an option (tick box) to set the primary key to autoincrement. Unfortunately, this option isn't proposed by default on table save when you create your table via the table design mode - to the extent that this mode is intended for people who are supposed to know what they are doing, I suppose it is understandable that the user has to make a positive decision to deliberately assign an autoincrement statement in the table design UI, rather than being asked for one, as the counter argument could be made that were I database designer having used the "manual mode", I would find it annoying to be asked each time I defined an ID primary key whether or not I wanted to assign the autoincrement function to that key.
Comment 2 Alex Thurgood 2017-05-26 08:54:01 UTC
Having said that, I agree with you that I use AUTOINC far more often on an INT column than just specifying an INT column that I have to fill manually with unique values. So, let's add this as an RFE, and see if there are any takers.
Comment 3 Mike Kaganski 2017-10-19 14:49:49 UTC
Actually, there is even more important use case for this proposal. Comment 0 deals with a case when user creates the ID manually; the program should guess the "AutoValue" setting based on user's provided data.

But consider the following scenario:
1. User creates a new table in Design View mode.
2. User *does not* create a key field manually.
3. On "Save", Base detects the absence of a key field, and emits the 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?

Upon selecting "Yes", a new key field named "ID" is created, of type INTEGER. In this case, the AutoValue isn't set by default, either! But that is clearly wrong, even if one could see the original use case as something arguable. When the key field was created automatically by program, then failing setting AutoValue is absolutely against user's expectations, and just trying to use resulting table as is, without further adjusting the key field, will result in error because of key field not set.
Comment 4 mhonline 2019-10-31 20:51:43 UTC
At actual, it is not possible to set an IDxyz-Field to primary-key and Auto-increment at once while building a new table.
The setting "auto" will get lost on closing the table.
When reopening for to change to auto, field-order changes (== drop+append), which is annoying.