Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let's say I have two tables, foo and bar:

   foo_id    serial    NOT NULL,
   count     integer   NULL,
   PRIMARY KEY(foo_id)

   bar_id    serial    NOT NULL,
   foo_id    SERIAL    NOT NULL REFERENCES foo(foo_id),
   PRIMARY KEY(bar_id)

When something gets inserted into bar with a given foo_id, the corresponding record in foo should have its count incremented. How exactly do I go about doing this? I'm fairly new to databases in general.

For example:


At the second line, count should be incremented at the record for foo_id = 1.

I also need to the count decremented on a delete, but I'm sure that's pretty similar to incrementing on insert.

share|improve this question

migrated from May 4 '14 at 1:47

This question came from our site for system and network administrators.

Recognising that this count is denormalised, and assuming you need it for some purpose that can't be obtained on-the-fly at run time, such as a COUNT() or EXISTS(), I would suggest you use a trigger on bar. More specifically, three triggers, one each for update, insert and delete since each will have a different effect on foo. Note, too, that you may need equivalent triggers on other bar-like tables which also have a foreign key pointing back to foo, depending on what your specific needs are.

In this case a trigger has the advantage of separating "proper" business rules (which should be in a stored procedure) from system optimisation. The former can be maintained by developers without the latter cluttering the code. Secondly, there is less risk that the optimisation code is compromised during a code change. Third, future SPs cannot forget to implement this optimisation or choose not to implement it. The trigger is there for all usage of the table. If there is a case when the count should not be updated that can be made explicit in the trigger code and well commented there for all to see.

share|improve this answer
While I agree with you, I have to add a con: with many of these triggers it will be very hard to follow what happens on a simple INSERT. I used to spend much time debugging funny behaviour, partly induced by my denormalization triggers. – dezso Nov 12 '14 at 9:23

Follow your INSERT statement with:

UPDATE bar SET count = (count+1) WHERE foo_id=1

And on delete:

UPDATE bar SET count = (count-1) WHERE foo_id=1
share|improve this answer
.. and wrap all these statements in a transaction, with good error trapping and handling. – Michael Green Nov 12 '14 at 1:46

I think you need to question why you are storing a count on the foo table. You will be able to retrive the number of bar records referencing any given foo_id at a later date by using SELECT COUNT(*)...

share|improve this answer

You should be able to retrieve the count by running

ON foo.foo_is = bar.foo_id
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.