0

I would like to know how to decrement a field (projects) in another table (users) after deleting an entry in a table (projects).

Users table:

+-----------+--------------+------+-----+---------------------+----------------+
| Field     | Type         | Null | Key | Default             | Extra          |
+-----------+--------------+------+-----+---------------------+----------------+
| id        | int(11)      | NO   | PRI | NULL                | auto_increment |
| username  | varchar(50)  | NO   | UNI | NULL                |                |
| password  | varchar(255) | NO   |     | NULL                |                |
| superuser | tinyint(1)   | NO   |     | 0                   |                |
| projects  | int(11)      | YES  |     | NULL                |                |
| date      | date         | NO   |     | current_timestamp() |                |
+-----------+--------------+------+-----+---------------------+----------------+

Projects table:

+---------+--------------+------+-----+---------------------+----------------+
| Field   | Type         | Null | Key | Default             | Extra          |
+---------+--------------+------+-----+---------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL                | auto_increment |
| name    | varchar(50)  | NO   | UNI | NULL                |                |
| creator | varchar(255) | NO   | MUL | NULL                |                |
| visits  | int(11)      | NO   |     | 0                   |                |
| file    | varchar(50)  | NO   |     | NULL                |                |
| date    | date         | NO   |     | current_timestamp() |                |
+---------+--------------+------+-----+---------------------+----------------+
Barmar
  • 741,623
  • 53
  • 500
  • 612
Warriorjacq9
  • 55
  • 5
  • Use an `AFTER DELETE` trigger on the first table that updates the second table. – Barmar Sep 23 '22 at 16:18
  • 5
    It would be better to avoid having that fields in the `users` table in the first place. If you need to know how many projects the user has, join with a query that uses `COUNT()` from the `projects` table. – Barmar Sep 23 '22 at 16:19
  • How are the two tables related? There doesn't seem to be a `user_id` foreign key in the `projects` table. – Barmar Sep 23 '22 at 16:27

1 Answers1

1

Denormalization in MySQL is not that easy.

Generated stored columns cannot work across tables, and views are not materialized as well.

Flexviews ( https://github.com/greenlion/swanhart-tools ) was warmly recommended in O'Reilly's book, High Performance MySQL 3rd edition, but it is discontinued for newer version of MySQL and would either way mean you had to setup your MySQL server yourself (cannot used managed cloud DBs).

This means you are left with

  1. Triggers in MySQL
  2. Doing the update from your application code

What you choose depends on your setup, but using triggers should be done with caution. They are easy to overlook and can cause deadlocks/performance hits, that are hard to debug.

Read more about triggers here: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

thephper
  • 2,342
  • 22
  • 21