ON UPDATE CASCADE by FOREIGN KEY??? |
Post Reply |
Author | |
djju1029
Senior Member Joined: Aug 02 2016 Location: the US Status: Offline Points: 126 |
Post Options
Thanks(0)
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?
|
|
Dmit OE
Admin Group Joined: May 31 2012 Status: Offline Points: 5283 |
Post Options
Thanks(0)
|
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. |
|
Dmit OE
Admin Group Joined: May 31 2012 Status: Offline Points: 5283 |
Post Options
Thanks(0)
|
P.S. If you want to automatically insert something, either change the INSERT query, or eventually try to use Triggers
|
|
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |