Question 114839 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Updating primary key record on foreign key record change

I have a one to many relation with Postgres.
The primary key entity LUT is dependent on its child entities.
I am looking for a way to cascade changes in the foreign key entities to the primary entity and update its LUT.

Since there can be many changes a solution with some kind of debounce will be better.

Example structure:
Table of primary entities:

Buildings

id address name updated_at
1 xxxxx aaa 2022-08-26 09:23:57.768+00
2 qqqqq bbb 2022-08-27 10:20:57.768+00
3 wwwww ccc 2022-08-27 09:20:57.768+00

Table of child entities – building_id is foreign to the buildings table:

Floors

id building_id rooms_number updated_at
1 1 5 2022-08-26 08:24:54.668+00
2 2 4 2022-07-25 11:25:37.712+00
3 1 3 2022-08-23 07:07:21.432+00
4 1 5 2022-08-29 09:19:58.765+00

I need that every time a floor is being updated, the updated_at of its building (foreign key entity) will also be updated as well i.e. its updated_at will update as well.

Trying to achieve it with DB functionality and not in the code. one of the issues is that many floors can be updated simultaneously. This is why I asked about debouncing for not having many updates of the building.

Thoughts?

Tags:

2

Answers


  1. 0

    Make sure that your foreign key relationships have ON UPDATE CASCADE specified, and the foreign key will automatically update to match the primary key.

    Login or Signup to reply.
  2. 0

    You basically have 2 options:

    1. Keep updated_at column in building and develop triggers to synchronize the update_at columns between building and floor tables. This is the process you currently have.
      Advantage: Updated_at column for building easily accessible through simple direct query.
      Disadvantage: Requires additional code, perhaps quite complicated, to select the correct updated_at from floor table. Consider, initial floor entry contained incorrect building_id. What does the trigger need to do when corrected. How about a delete from floor; might it contain the current building updated_at? .

    2. Do not actually store the updated_at column on the building table.
      Instead create a view that derives the latest updated_at column from floors table.
      Advantage: No further action (code) required on DML operations.
      Updated_at column for buildings always shows latest floor updated value (if any).
      Disadvantage: Cannot issue DML directly against building as it is a view (keeping same name) as the underlying table has a different name. This would require Instead of triggers to update actual table or each developer to have knowledge of the underlying table.

    IMHO option 2 is by far the superior. It follows the basic relational model dictate of do not store what is derivable. Is it without issues? Clearly not, you will probably want to build instead of triggers. And there could be others. See here a short example containing each option.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search