Ask Your Question
0

delete cascade seems not to work

asked 2020-08-20 11:47:42 +0200

LeoP gravatar image

updated 2020-08-20 11:52:13 +0200

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:

image description

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:

image description

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?

edit retag flag offensive close merge delete

Comments

Have you tested without redundancy.

m.a.riosv gravatar imagem.a.riosv ( 2020-08-20 21:16:59 +0200 )edit

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

LeoP gravatar imageLeoP ( 2020-08-21 14:01:33 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-08-21 20:24:29 +0200

Ratslinger gravatar image

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:

image description

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:

image description

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.

edit flag offensive delete link more

Comments

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/fil...

LeoP gravatar imageLeoP ( 2020-08-21 21:13:12 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2020-08-21 22:25:59 +0200 )edit

@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;
cpb gravatar imagecpb ( 2020-08-22 03:53:03 +0200 )edit

@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

Ratslinger gravatar imageRatslinger ( 2020-08-22 04:53:25 +0200 )edit

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 gravatar imageLeoP ( 2020-08-22 09:12:04 +0200 )edit

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.. :)

LeoP gravatar imageLeoP ( 2020-09-14 09:49:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-20 11:47:42 +0200

Seen: 60 times

Last updated: Aug 21 '20