How LO base handles relationships on mariadb?

[tor2]

I’m having troubles understanding how relationships are managed by LO base, specifically fk constraints “on delete”.
Database is a simple three table test: book - author and bookauthors connecting the many to many relation between the two.
For example I’d like the fk bookauthors.authorid changed if the master author.authorid changes - ON UPDATE CASCADE radio button in LO relationships dialog does that and works as I expected as well as does setting it with ‘mysql’ command.

In ‘mysql’ I can set ON DELETE NO ACTION but that is not reflected in LO relationships dialog - the ON DELETE CASCADE radio button is now active.

Further if activating NO ACTION radio button in LO and then checking in ‘mysql’ with ‘show create table bookauthors;’ the ON DELETE NO ACTION is gone, eh…

Setting ON DELETE CASCADE in LO dialog has the desired effect, if after that changed to NO ACTION the ON DELETE CASCADE is erased leaving the ON UPDATE… alone (when checked in ‘mysql’).

I understand that NO ACTION is the default in mariadb but it is somewhat odd to find that LO deletes a definition that is already set and to me somewhat bewildering that LO shows ON DELETE CASCADE active when relations dialog is reopened…

I’m no database guru so my questions: what am I missing, is this the correct behaviour or what errors did I do. ?

LO 7.3.5.2
MariaDB 10.5.16

[Villeroy]

This is data definition language (DDL) and belongs entirely to the backend. Base is a frontend tool. Use your favourite administration tool for MySQL or Base’s simplistic Tools>SQL window.

[tor2]

Thank’s for your answer,

I didn’t mean to criticize but actually was hoping Base would be my favourite admin tool

Do you mean Base is not the tool for simple DDL when working with MariaDB ?

Unfortunate I think, it looked the perfect tool for newbies like myself…

[Villeroy]

Base is a tiny addition to this office suite connecting databases with office documents. The overall footprint, including several database drivers, is no more than 40 MB. Base is not a database. Base is not a database development environment.
The vast majority of Base documents is connected to spreadsheets in order to facilitate serial letters and label printing.

[tor2]

“Base is not a database development environment.
The vast majority of Base documents is connected to spreadsheets in order to facilitate serial letters and label printing.”

Ahh, my bad.

[Ratslinger]

Hello,

I have used Base with various databases for years including MySQL (tries MariaDB just to answer a question here) PostgreSQL, HSQLDB (various flavors) and others.
.
Currently use PostgreSQL as primary personal DB.
.
Issues crop up all the time and here is no different:

@tor2

I understand that NO ACTION is the default in mariadb but it is somewhat odd to find that LO deletes a definition that is already set and to me somewhat bewildering that LO shows ON DELETE CASCADE active when relations dialog is reopened…

is erroneous and should be reported here → Bugzilla.
.
I received the same result using MySQL. However I could see the correct setting using Tools->SQL... and entering:

SHOW CREATE TABLE yourTableName;

.
BTW, there are a lot of users using Base for other than “connected to spreadsheets”. Just search this site using Base or database as a keyword.
.
Edit:
Forgot to mention, trying to delete the author did result in an error. So the relation was incorrectly shown but it worked correctly.

[tor2]

Filed a bug: 150854

[tor2]

Thank you for confirming my experiences.
I was a little worried there was some misunderstanding on my part…

Ratslinger, as you pointed out, relationships seems to work despite the presentation.

Filed a bug and I’m satisfied with this conclusion.