Indicate the foreign key about change in data

Question

Hi I am using foreign key reference in table 2. Suppose user has changed some data in the table 1 which is referred as foreign key in table 2. Is there any way (or some flag) to indicate in the table 2 that data has been changed in table 1. Please suggest me if this is possible or not. I am using MySql database.

Per my comment on Mr. White's answer, could you clarify what behavior you are looking for? Do you just want `table 2` to reflect the updated information, or do you want to set a value in another column of `table 2` when the corresponding row is changed in `table 1`? — user812786, Jan 27 '16 at 16:19

score 0 · Answer 1 · answered Jan 27 '16 at 15:19

0

The answer depends on the type of database engine you are using as well as the referential actions you have defined on the aforementioned tables.

These are all the types of referential constraints you can impose:

CASCADE
SET NULL
RESTRICT
NO ACTION 
SET DEFAULT

Here is an example:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

Source: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

answered Jan 27 '16 at 15:19

Dennis

3,962
7
26
44

This works for updating / deleting automatically, but won't "flag" updated information, which it sounded to me like OP was asking for. @Ishan could you clarify? – user812786 Jan 27 '16 at 16:16
@whrrgarbl yes you are right. To be more clear I want to indicate in `table 2` user that `table 1` has been modified, so please modified rest of columns in `table 2` accordingly. As the calculation of rest of the columns depends on the foreign key column's value – Ishan Jan 27 '16 at 18:30
In that case you can just create a trigger that fires `before delete` when the user tries to delete something that does not exist in the parent table. – Dennis Jan 28 '16 at 08:19

score 0 · Accepted Answer · edited May 23 '17 at 12:09

Mr. White's answer is correct for updating / deleting automatically, but won't "flag" updated information, which is how I interpreted the question. If you want to set the value of another "flag" column when a row in table 1 is updated, you should look into triggers.

Note that the docs say:

Cascaded foreign key actions do not activate triggers.

So setting up table 1 with the CASCADE keyword will not activate any custom code in the foreign key table (table 2) in your example - you would have to put the trigger on table 1 instead.

From the docs, possible trigger events are:

INSERT - whenever a new row is inserted (via INSERT, LOAD_DATA, REPLACE, etc.)
UPDATE - whenever a row is modified
DELETE - whenever a row is deleted (via DELETE, REPLACE, but not DROP_TABLE or TRUNCATE_TABLE)

and can go either BEFORE or AFTER the event. In your case, AFTER would be appropriate, as it will only execute if the statement triggering it was successful. The body of your trigger can be a statement or stored routine, the contents of which will of course depend on your actual table structures.

A quick search turned up a number of questions about defining triggers with this sort of behavior, which might point you in the right direction for your specific setup:

More info on using triggers: https://dev.mysql.com/doc/refman/5.6/en/triggers.html

Thanks its helps me in understanding!! – Ishan Feb 02 '16 at 12:51 — Ishan, Feb 02 '16 at 12:51

Indicate the foreign key about change in data

2 Answers2