There are two groups and students tables

groups id | name | 1 | BB-31 | 2 | BB-32 | 3 | PR-41 | students id | group | 1 | BB-31 | 2 | PR-41 |

The groups.name and students.group weighs the forgeign key . How to do something to change the data in groups.name they immediately changed to students.group ?

    1 answer 1

    It is very wrong to hang a connection between tables on a variable field like name . Usually a so-called surrogate key is added to each table, the same id field of the type INT NOT NULL AUTO_INCREMENT .

    At the very least, this solution will save you from having to simultaneously change the fields in the related tables.

    But, if such a change is still needed, use foreign keys with the cascade update option.

    When declaring a table of students , describe the foreign key :

     CREATE TABLE students ( id INT NOT NULL AUTO_INCREMENT, `group` NOT NULL VARCHAR(100), PRIMARI KEY (id), FOREIGH KEY (`group`) REFERENCES groups (name) ON UPDATE CASCADE );

    We enclose the name of the group field in reverse apostrophes, because it coincides with the beginning of the GROUP BY keyword.

    Now, when the groups.name field changes, the field students.group of all students from this group will change.

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