Updating two tables in a many-to-one relationship through a view in PostgreSQL

Question

I have two tables: foos and bars, and there is a many-to-one relationship between them: each foo can have many bars. I also have a view foobars, which joins these two tables (its query is like select foo.*, bar.id from foos, bars where bar.foo_id=foo.id).

EDIT: You would not be wrong if you said that there's a many-to-many relationship between foos and bars. A bar, however, is just a tag (in fact, it is a size), and consists just of its name. The table bars has the same role as a link table would have.

I have a rule on inserting to foobars such that the “foo” part is inserted to foos as a new row, and “bar” part, which consists of a couple of bar-id's separated by commas is split, and for each such part a link between it and the appropriate foo is created (I use a procedure to do that).

This works great for inserts. I have a problem, however, when it comes to updating the whole thing. The foo part of the rule is easy. However, I don't know how to deal with the multiple bars part. When I try to do something like DELETE FROM bars WHERE foo_id=new.foo_id in the rule, I end deleting everything from the table bars.

What am I doing wrong? Is there a way of achieving what I need? Finally, is my approach to the whole thing sensible?

(I do this overcomplicated thing with the view because the data I get is in the form of “foo and all its bars”, but the user must see just foobars.)

Answer 1

I don't think new.foo_id is correct in the context of a delete.

Shouldn't it be DELETE FROM bars WHERE foo_id=old.foo_id?

Answer 2

Rysiek, if I understood correctly, you have text column in foos table that is parsed to extract foreign keys pointing to bars table. This approach to building relations may be justified in some cases, however almost every guide/tutorial to database programming would discourage doing so. Why not use standard foreign key in bars that would point to foo in foos? Unless there is a requirement for bars to be assigned to more than one foo. If so, this identifies your relation as many-to-many rather one-to-many. In either situation using standard foreign key based solution seems much more natural for database.

Example db schema for one-to-many relation:

CREATE TABLE foos (
    id SERIAL PRIMARY KEY,
    ....
);
CREATE TABLE bars (
    id SERIAL PRIMARY KEY,
    foo_id INT REFERENCES bars (id) ON DELETE CASCADE,
    ...
);

And the same for many-to-many relation:

CREATE TABLE foos (
    id SERIAL PRIMARY KEY,
    ....
);
CREATE TABLE bars (
    id SERIAL PRIMARY KEY,
    ...
);
CREATE TABLE foostobars (
    foo_id INT REFERENCES foos (id) ON DELETE CASCADE,
    bar_id INT REFERENCES bars (id) ON DELETE CASCADE
);

I would also recommend using INNER JOIN instead of table multiplication (SELECT FROM foos, bars).

CREATE VIEW foobars AS
SELECT
    foos.id AS foo_id, foos.something,
    bars.id AS bar_id, bars.somethingelse
FROM foos
INNER JOIN bars ON bars.foo_id = foo.id;

The same for many-to-many INNER JOINS

CREATE VIEW foobars AS
SELECT
    foos.id AS foo_id, foos.something,
    bars.id AS bar_id, bars.somethingelse
FROM foos
INNER JOIN foostobars AS ftb ON ftb.foo_id = foo.id
INNER JOIN bars ON bars.id = ftb.bar_id;

Answer 3

This is how I have actually dealt with it: when I get a unique constraint violation, instead of updating I simply delete the foo and let the cascade take care of the bars. Then I simply try to insert once again. I have to use more than one SQL statement to do it, but it seems to work.

Answer 4

The deletion problem is that you are deleting on a predicate that is not based on the table you are deleting from. You need to delete based on a join predicate. This would look something line:

delete b
  from foo f
  join foobar fb
    on f.FooID = fb.FooID
  join bar b
    on b.BarId = fb.BarID
 where f.FooID = 123

This lets you jave a table of Foo's, a table of Bar's and a join table that records what Bar's the Foo has. You don't need to compose lists and split them apart. This is a bad thing because the query optimiser can't use an index to identify the relevant records - in fact this violates the 1NF 'No repeating groups' rule.. The correct schema would look something like:

Create table Foo (
     FooID int
    ,[Other Foo attributes]
)

Create table Bar (
     BarID int
    ,[Other Bar attributes]
)

Create table FooBar (
     FooID int
    ,BarID int
)

With appropriate indexes, the M:M relationship can be stored in FooBar and the DBMS can store and manipulate this efficiently in its native data structures.