ON UPDATE CASCADE by FOREIGN KEY???

Printed From: openElement Website
Category:

openElement

Forum Name: Dynamic OE - Databases, etc
Forum Description: openElement's database management system and Element Packs
URL: https://forums.openelement.uk/en/forum_posts.asp?TID=837
Printed Date: Apr 19 2024 at 1:42pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com

Topic: ON UPDATE CASCADE by FOREIGN KEY???

Posted By: djju1029
Subject: ON UPDATE CASCADE by FOREIGN KEY???
Date Posted: Sep 19 2016 at 5:17pm

I want to use a value in child table by ON UPDATE CASCADE on FOREIGN KEY relationship from a parent table.

I set the relationship from parent to child using FOREIGN KEY, for instance:

// parent

CREATE TABLE `test_table` (

`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

`name` TEXT

)

// child

CREATE TABLE 'test_table_child2' (

`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'child_name' TEXT,

auto INT NOT NULL,

FOREIGN KEY (auto) REFERENCES test_table(id)

ON DELETE CASCADE

ON UPDATE CASCADE

)

However, the 'auto' could be changed even though the 'id' of 'test_table' was changed.

Above code is my test code.

My ultimate code is that if the primary key in parent table will be INSERTed, the child primary id key should be inserted automatically.

Is it possible on OE?

Replies:

Posted By: Dmit OE
Date Posted: Sep 19 2016 at 11:38pm

Hi,

First, let's separate OE from MySQL/PHP. Your example will work the same way in a Code Block or in a manually written script called directly in a browser. Now, do you manage to make it work as you need? As far as I know the UPDATE CASCADE is supposed to update the referencing field (`auto`) when the referenced field's value (`test_table.id`) is changed. Not vice versa - you are free to "link" a row in test_table_child2 to another test_table's row for example. DELETE CASCADE will delete the test_table_child2's row if corresponding test_table's row is deleted - not vice versa either.

Posted By: Dmit OE
Date Posted: Sep 19 2016 at 11:39pm

P.S. If you want to automatically insert something, either change the INSERT query, or eventually try to use Triggers