I want to add a non unique index to a table?
I want to index foreign keys to (hopefully) speed up one-many joins.
Is there a way to tell LO base about PK-FK relationships? If so how?
How do I do this in LO base:
with an embedded database?
with a remote mariadb? (connected as mysql, jdbc)
Hello,
This information is basically obtained from the appropriate documentation of the database you are working with. For example, in HSQLSB v1.8 embedded ( LO current default) a unique constraint is:
ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] UNIQUE (<column list>);
Please see the docs for v1.8 Chapter 9 for more information → Hsqldb User Guide
For MariaDB the documentation is here → MariaDB Documentation
Also, many relations can be set from the main Base screen menu under Tools->Relationships. For this, see the LO documentation found here → LibreOffice Base Handbook in Chapter 3 - Tables.
Thanks that’s helpful
What I also needed to know (and found by luck) was where to enter non-SELECT queries.
For anyone else similarly stuck, you don’t enter them as a query in the UI; they go under Tools → SQL from the main menu bar.
Also these queries do not seem to be saved. Remember to ctrl-a ctrl-c and copy the text into a file somewhere. If you don’t do so, and make a trivial mistake, you have to type it all again (please correct me if I am wrong – in this case I would love to be)
@trueriver Actually, you can store & sometimes process non-SELECT statements in the Query section by clicking on the Run SQL command directly icon on the toolbar. Storage there is helpful for all DB types. However please note only some external DB’s will execute from there but not for all statements. It’s a bit of trial & error. Embedded will NOT execute from there but you can store the statements for future use.
Also, peruse the documentation as it will contain much of this information