BullGuard Antivirus Sale - 60% Off with openElement !
Forum openElement Homepage
Forum Home Forum Home >

openElement

> Dynamic OE - Databases, etc
  New Posts New Posts RSS Feed - ON UPDATE CASCADE by FOREIGN KEY???
  FAQ FAQ  Forum Search   Register Register  Login Login

ON UPDATE CASCADE by FOREIGN KEY???

 Post Reply Post Reply
Author
Message
djju1029 View Drop Down
Senior Member
Senior Member
Avatar

Joined: Aug 02 2016
Location: the US
Status: Offline
Points: 126
Post Options Post Options   Thanks (0) Thanks(0)   Quote djju1029 Quote  Post ReplyReply Direct Link To This Post Topic: ON UPDATE CASCADE by FOREIGN KEY???
    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?
Back to Top
Dmit OE View Drop Down
Admin Group
Admin Group
Avatar

Joined: May 31 2012
Status: Offline
Points: 5007
Post Options Post Options   Thanks (0) Thanks(0)   Quote Dmit OE Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Dmit OE View Drop Down
Admin Group
Admin Group
Avatar

Joined: May 31 2012
Status: Offline
Points: 5007
Post Options Post Options   Thanks (0) Thanks(0)   Quote Dmit OE Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 10.11
Copyright ©2001-2012 Web Wiz Ltd.

This page was generated in 0,031 seconds.