Can't define a primary key from an imported calc spreadsheet tables

Earendil1 · August 21, 2023, 8:13pm

Hello! I’m very very new to Base or to databases, it’s my first attempt to learn and use such tools.

I’m trying to set a primary key for a database I imported from Calc, that contain three spreadsheets, each with its own table. Those tables are structured the same way - the data changes, but the headers are the same, the logic is the same.

I managed to create a database where those table appear, by selecting 'connect to an existing database, selecting spreadsheet in the drop-down menu, browsing and selecting the Calc file, then leaving the rest as by default (yes, register the database for me ; Open the database for editing).

However, in the Tables section, when I right-click on any of them and select ‘edit…’ in the contextual menu, a window opens where I’m supposed to be able to define what field is the primary key, but selecting the field’s row then right-clicking on it doesn’t open a contextual menu ; moreover, the field properties zone is greyed out. Is it a bug? is it a limitation of base or the method I used to create those tables in base? Is it my lack of knowledge? probably, for the last one.

How can I set that field as a primary key?

Below, a copy of the Calc file. I’m not asking for anybody to do the work, as I understand this is a community of enthusiasts that aren’t paid for tech support, but if it can help contextualize the my description, here it is.
tabela tipo vinho.ods (181.9 KB)

Villeroy · August 21, 2023, 8:16pm

Create a database with all the columns in tables, all the indices, keys, relations, auto-IDs. Then copy any existing data from spreadsheets.

Earendil1 · August 21, 2023, 8:31pm

hmm. I don’t get what you refer to, but I’d hoped that I wouldn’t have to do that again. Many columns in the Calc file have validity list entries, for example. I’ve dedicated quite a bit of time making that file. It’s not very complex, but it’s still a lot of work done, that ideally I wouldn’t want to repeat if possible.

That’s why I tried to create the database importing the calc file - to have those tables imported.

Villeroy · August 22, 2023, 8:55am

Concentrate on your business and keep on working with spreadsheets.

RobertG · August 22, 2023, 9:11am

The tables of Calc aren’t imported to any database. So you couldn’t change anything in the table.

If you will try it with a database: Don’t connect Base to Calc file. Import Calc tables to a standalone database file like internal HSQLDB or Firebird.

@Earendil1 : When I see the Calc file all tables will have the same structure. Seems you only need one table in your database file and seems you got already a primary key (“Identific.”). So import one of the tables and add Data of the other tables all to the one table.

Earendil1 · August 22, 2023, 9:28am

@Villeroy
What’s with this passive aggressive response? Why do you presume about me, the work that I’m doing, or the business I have?

I’m not getting paid for this.
I’m actually trying something new with LibreOffice in the time can spare, because I see the tool I need to deal with some problems is a database.
It’s not my business.

Giving me life advice is quite a bit patronizing, It has noting to do with this topic or LibreOffice, It’s not productive at all. It’s just lashing out gratuitously, It doesn’t have a valid purpose, it’s just toxic.

If you don’t feel like helping or explaining, then just don’t.

Earendil1 · August 22, 2023, 9:30am

@RobertG
Thank you for your explanations, I’ll try that out.

Villeroy · August 22, 2023, 12:51pm

tipo_vinho.odb (40.0 KB)

Earendil1 · August 22, 2023, 1:04pm

@Villeroy

Thank you for helping me. I see I may have reacted a bit hotly, and I am grateful you took some of your time to give me a glimpse of a LibreOffice Base file.
I know I will mess up a lot, that I don’t know what I should to do it right, but messing with things is usually a good occasion to learn and acquire new skills.

If you have a resource you think that could be profitable for me to learn on how to create a database that fits with my case in LibreOffice Base, I’m interested in what you’d recommend as well.

JohnSUN · August 22, 2023, 1:19pm

Do you prefer to read? Or watch and listen?

Villeroy · August 22, 2023, 1:23pm

First thing to understand is that LibreOffice Base is not a database. It is a tool set to work with databases in the context of this office suite. It is most useful when you already have some database up and running and need to add some user interface with meaningful queries, input forms and printable reports.

The creation of a database is development work. It requires some theoretical knowledge and expertise which is the same for all relational databases (SQL databases) of the past 40 years.
The database document I created from your sheet data is an embedded HSQLDB. See status bar of the database window. This is a type of database LibreOffice Base can create from scratch. It is embedded in the database document.
I merged the 3 categories from different sheets into one produtor table. I created 3 additional tables for the categories tipo, cor and conteudo.
The names of these categories are always the same throughout your spreadsheet. Each vinho has one value of a category. This gives 4 one-to-many relations from vinho to contenudo, cor produtor and tipo.

You stored the castas as comma separated lists. I split them up into separate names and a mapping table mapping castas to vinhos. This is a many-to-many relation.
Open menu:Tools>Relations…
You see the 4 one-to-many relations on the left side and the many-to-many relation with its mapping table on the right.