Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

CREATE TABLE foo(
   foo_id    serial    NOT NULL,
   count     integer   NULL,
   PRIMARY KEY(foo_id)
   );

CREATE TABLE bar(
   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:

INSERT INTO foo VALUES(1, 0);
INSERT INTO bar VALUES(1, 1);

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 serverfault.com May 4 at 1:47

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

3 Answers 3

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

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

SELECT COUNT(*) FROM foo 
INNER JOIN bar
ON foo.foo_is = bar.foo_id
share|improve this answer

Your Answer

 
discard

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