Bug 64377 - DATABASE: Please automatically add a hidden ROWID field while creating tables.
Summary: DATABASE: Please automatically add a hidden ROWID field while creating tables.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.0.0.2 rc
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: 61547
  Show dependency treegraph
 
Reported: 2013-05-09 04:59 UTC by Biju
Modified: 2020-10-22 15:25 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Biju 2013-05-09 04:59:51 UTC
Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3)
TinderBox: MacOSX TDF Release, Branch:libreoffice-4-0, Time: 2013-03-26_15:52:16

DATABASE: Please automatically add a hidden ROWID field while creating tables.

When ever user create a new table in BASE, LibreOffice should automatically add a hidden ROWID field. It should be a AutoValue = true field and should act like a primary key field for the purpose of editing.

At present 
1. if you create a table with out primary key or if you uncheck "primary key" property of a field on existing table with primary key. The table become non-insertable/non-update-able.

2. Additionally from GUI, you will not be able to add a primary key field to the table or turn an existing field to a primary key.

I believe fixing this should solve issues like Bug 61547 

Also please see bug 56242, bug 37333
Comment 1 Robert Großkopf 2013-05-09 07:32:47 UTC
I wouldn't prefer an automatically added and hidden primary-key-field. 
When you create a table without a primary-key a dialog in Base appears:
"No primary key ... you can only enter data with a primary key ... should the primary key created now?"
At this moment the user has only to press a button and will see the primary key in his table. He will be able to create relations between tables, because the primary-key isn't hidden and he could see the field-type of this key.
For many people the primary-key with autovalue and field-type "Integer" is the default. But there are also tables, which would work better without this automatic.
Example: 
table - "Gender"
fields - "ID", "Gender", "Title", "Salutation"
"ID" would be varchar with 1 char. Content of this field would be "m" and "f", while the content of "Gender" is "male" and "female".
I also use tables with only one row for settings of the database, where I change the value. Primary key in this tables has the field-type "Bool".

The only bug in this moment is: It isn't possible to add a primary key in an existing table without primary key while using the GUI. It is possible to create such a value using the direct SQL-mode. You have to decide the creating of a primary-key when creating the table. There is a warning, when you will save a table without creating such a key. If somebody ignores this warning it should be possible to change this with the GUI - and not hidden. But this is https://bugs.freedesktop.org/show_bug.cgi?id=61547
Comment 2 Biju 2013-05-12 00:39:51 UTC
Please Note

(In reply to comment #1)
> I wouldn't prefer an automatically added and hidden primary-key-field.

The request is not asking to add hidden primary-key-field.
It is asking "add a hidden ROWID field",
which "act like a primary key field" at certain times. 
may be some thing similar to what Oracle have.

Only problem in Oracle is there is no guaranty that ROWID of a record stay constant for the life time of the record. It changes when we/system reorg a table.
 
> When you create a table without a primary-key a dialog in Base appears:
> "No primary key ... you can only enter data with a primary key ... should
> the primary key created now?"

Not much help for a new bee.
Basically most first time users dont understand the concept of primary key, foreign key, and relational integrity.  

> The only bug in this moment is: It isn't possible to add a primary key in an
> existing table without primary key while using the GUI. 

No, I dont think you need to force user to create a primary key all the time.
Oracle, MS-SQL, MS-Access dont do that.
Still corresponding GUI tools like SQL-Developer, TOAD, SQL-Server Management Studio, and also I think MS-Access allow user to edit the table content.
Comment 3 Alex Thurgood 2015-01-03 17:39:30 UTC Comment hidden (no-value)
Comment 4 mhonline 2020-10-22 15:25:39 UTC
what about this?
Having row-numbers available by default is a good concept for "natural" search-order, and up to my knowledge HSQL has it build in - while LO does not make use of it - but FB does not have them and creates it temporary on any query, so this needs to be added.
Although it does not need to be a primary key, the order of entry should be honored - it will be useful for example to simply create unique keys like Meier-1, Meier-2 etc.

mh