Delete cascade seems not to work

[LeoP]

I have a database with parts, each of which can have multiple vendors. Each vendor-part combination can have multiple pricebreaks. Relations look like this:

The vendor and order_code fields in the pricebreaks are redundant, they are only there to facilitate maintenance.
The relations parts↔part-vendor and part-vendor↔pricebreaks are set to update cascade and delete cascade.
For everyone’s convenience I attached the database (not complete yet).

Data is managed with a form, containing a subform for the part vendors, which in turn has a subsubform for the pricebreaks.
Data entry works like a charm, but when I want to delete a parts record (and expect all associated part-vendor and pricebreak records to be deleted as well because of the cascade setting), I get an error:

The same error (different table) occurs when I want to delete a part-vendor record…
Apparently, the only way to delete a parts record is to start with the pricebreak records, then the part-vendor records and finally the parts record itself…

How do I get the cascade function to work as expected?

[mariosv]

Have you tested without redundancy.

[LeoP]

I did just now: no joy, same error.
Made a test-part with bogus information, two vendors, two pricebreaks each, nothing got deleted…

[Ratslinger]

Hello,

Your question states:

For everyone’s convenience I attached the database

but there is nothing attached in the question. Did find a sample in tdf#135919. (Note: could not reproduce your problem there).

Using the database as is and trying to delete an item from parts got this message:

Your question states:

The relations parts↔part-vendor and part-vendor↔pricebreaks are set to update cascade and delete cascade.

but this is what I see in the sample:

There was no ‘cascade’ set for either Update or Delete on either of the two noted. Set the Delete cascade on both and had no problem or errors deleting parts which deleted respective part-vendor and pricebreaks records.

[LeoP]

I tried to add it to the original question, but that somehow failed (on three occasions)…
The example you found is a very early one.
One should be able to download it here: https://www.uploadarchief.net:443/files/download/inventory.zip

[Ratslinger]

@LeoP,

Again similar situation. part-vendor↔pricebreaks is not set for any cascade. Set Delete cascade and parts and related records deleted without a problem.

[cpb]

@LeoP,
Ratslinger has solved your issue.
the Base Relations Design window is buggy and unreliable so it’s worth double checking your foreign key relationships and in particular the update/delete rules.

paste the code into the ‘Query Design’ window, activate the ‘Run SQL command directly’ gadget and execute.

update/delete rule values are:
0 = cascade
1 = default
2 = set null
3 = no action

select distinct
    fk_name, 
    pktable_name, 
    pkcolumn_name, 
    fktable_name, 
    fkcolumn_name ,
	update_rule,
	delete_rule
from information_schema.system_crossreference

a foreign key can not be altered, it must first be deleted and then recreated.

alter table "part-vendor" drop constraint "FK_NAME"

when you create a foreign key via SQL you must also give it a name e.g. ‘FK_part_ID’
e.g.

alter table "part-vendor" add constraint "FK_part_ID" foreign key ("part_ID") references "parts" ("ID") on update cascade on delete cascade;

[Ratslinger]

@cpb,

I don’t agree with:

the Base Relations Design window is buggy and unreliable

The situation found here was corrected and worked in the Relationship window. Base has many open repair items and there is certainly something on the Relationship section but I would not tag it as “buggy and unreliable”.

If you find problems, then it is best to report → Bugzilla

[LeoP]

Just tested it on an older machine with LO 5.0.3.2, Build ID:1:5.0.3~rc2-0ubuntu1~trusty2 under Mint17.3 (64bit) version 2.8.8, kernel 4.4.0-148-generig: after setting the cascades it works as expected (I could have sworn that I set those cascades in the demo)…
Found out that the Relationship window does not alert the user to save things when closing it, apparently changes are stored immediate (in this version at least). Not so sure about LO6.something but cannot test that until monday…
Will keep you posted.

[LeoP]

Well, it is Monday, albeit three weeks later: some virus got in the way…

During those three weeks, updates were performed on the system I was working on, so here are the new details.

Kernel: 5.4.0-47-generic x86_64
Desktop: Cinnamon 4.6.7
Distro: Linux Mint 20 Ulyana
base: Ubuntu 20.04 focal
LibreOffice: 6.4.5.2, Build ID 1:6.4.5-0ubuntu0.20.04.1

To my amazement, it works as expected, the ‘problem’ (at least this one) is gone…