LO Base does not support “Relationship” for SQlite Database both as external or embedded mode. I am using “jdbcDriverOOo.oxt” and “SQLiteOOo.oxt” extension for Embedded mode. And unixODBC driver for external mode. Use chinook.db (SQLite Sample Database And Its Diagram (in PDF format)) for testing as external SQlite database. Someone please help
Facing the same problem as you have posted but no solution yet.
SQLite does not support relations. Use it as is, or use another database engine.
Actually, SQLite support ‘Foreign Key’ or ‘Relations’ in SQLite Studio but NOT In LibreOffice. Does any one has suggestion?
Use the tool that works for you.
I am looking for a easy, compatible and versatile front-end like LO Base for SQLite.
Then find a driver that supports the latest features, and I’m confident that Base will handle it. If you edit data through forms, subforms and list boxes enforce referential integrity, even if the backend does not.
Without referential integrity enforced by relations, a listbox lets you select items into a foreign key field that do exist in another table. Subforms can show only items that are related to the parent form’s selected item.
Then let’s read the documentation of Sqlite
Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:
sqlite> PRAGMA foreign_keys = ON;
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.
My ODBC-driver has in the odbc-configuration a checkbox for foreign-key, wich I have not tested…
As @Villeroy wrote: Use or compile a driver wich includes the necessary “support” for Sqlite or use a database, where this is not switched off by default. MariaDB/MySQL would be possible.
.
Sqlite is not provided by TDF/LibreOffice, so don’t expect Base to add functions. As you use an extension, you might ask the creator of the extension, how he compiled sqlite, and, if it is possible to support relations. But embedded sqlite is no regular feature of LibreOffice/Base.
Why do you create (embedded) SQLite if you have HSQL and Firebird at hand? This makes no sense. If you have some SQLite-DB from a third party application, but hat application has no decent mail merge, reporting or analysis tool, it makes sense to connect LO with that existing 3rd-party SQLite-DB.
Side note on terminology: A relational database tool which does not support relations is rather useless.
Consider terms “relation” and “relationship”. The developers seem to have confused '*these rather fundamental concepts. Can we then trust the products?
I know, this is not a discussion forum. I do not mean to initiate discourse, only instill some thought to the situation: The discordance made evident by the error message may be taken as a red flag; if you are looking for a reliable relational DB tool, look elsewhere. My experience with using both LO Base and SQLite has been generally good, but I haven’t used them in conjunction. Seeing the above, I probably won’t do in the future either.
I’m using both together since around 2018 (7 years) without problems. But when one uses Sqlite, one should read on the topics, where it is different from other databases - for example type-affinity instead of strict types etc.
.