Print Page | Close Window

ON UPDATE CASCADE by FOREIGN KEY???

Printed From: Forum openElement
Category:

openElement


Forum Name: Dynamic OE - Databases, etc
Forum Description: openElement's database management system and Element Packs
URL: http://forum.openelement.com/fr/forum_posts.asp?TID=837
Printed Date: 05 Juil 2020 at 15:00
Software Version: Web Wiz Forums 10.11 - http://www.webwizforums.com


Topic: ON UPDATE CASCADE by FOREIGN KEY???
Posted By: djju1029
Subject: ON UPDATE CASCADE by FOREIGN KEY???
Date Posted: 19 Sep 2016 at 18:17
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: 20 Sep 2016 at 00:38
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: 20 Sep 2016 at 00:39
P.S. If you want to automatically insert something, either change the INSERT query, or eventually try to use Triggers



Print Page | Close Window

Forum Software by Web Wiz Forums® version 10.11 - http://www.webwizforums.com
Copyright ©2001-2012 Web Wiz Ltd. - http://www.webwiz.co.uk