CODEWITHSUNDEEP
mysqlsqldatabase
user1182183
user1182183

Reputation:

How to create a table that automaticly updates another table when content is changed?

What I try to accomplish is that I want to update rows in tableA when one row from tableB gets deleted.

The layout of tableA is this:

+----------------------------+--------------+------+-----+---------------------+----------------+
| Field                      | Type         | Null | Key | Default             | Extra          |
+----------------------------+--------------+------+-----+---------------------+----------------+
| user_id                    | int(11)      | NO   | PRI | NULL                | auto_increment |
| nickname                   | varchar(32)  | NO   |     | NULL                |                |
| password                   | varchar(129) | NO   |     | NULL                |                |
| mafia_id                   | int(11)      | NO   |     | 0                   |                |
+----------------------------+--------------+------+-----+---------------------+----------------+

and of tableB this:

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| mafia_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| mafia_name  | varchar(32) | NO   |     |         |                |
| mafia_tag   | varchar(5)  | NO   |     |         |                |
| mafia_color | int(11)     | NO   |     | 0       |                |
| mafia_car   | int(11)     | NO   |     | 0       |                |
| mafia_base  | int(11)     | NO   |     | 0       |                |
+-------------+-------------+------+-----+---------+----------------+

I want to set all tableA.mafia_id to 0 when the corresponding mafia_id in tableB is deleted.

I read in the documentation that the database will automaticly do it for you, but you have to specify some stuff at table creation (in CREATE TABLE, create_definition: | CHECK (expr)?). The documentation is a bit unclear to me.

I also read this topic: Create a trigger that updates a column on one table when a column in another table is updated

but this doesn't apply to me, i think?

So how would I create such a table (create table ...) or delete row statement? Thanks in advance!

Upvotes: 0

Views: 191

Answers (1)

eggyal
eggyal

Reputation: 125865

Since you are using InnoDB, you can achieve this with a foreign key constraint:

ALTER TABLE tableA
  MODIFY mafia_id INT(11) NULL,
  ADD FOREIGN KEY (mafia_id) REFERENCES tableB (mafia_id) ON DELETE SET NULL

As explained in the manual:

  • SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

    If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

Note that the constraint has the additional advantage of ensuring that mafia_id values in tableA must always reference an existing record in tableB.

Upvotes: 2

Related Questions