Re: PL/pgSQL compatibility? - Mailing list pgsql-general

> Sorry to email you personally - I meant to send it to the mailing
> list. And to be even less polite I'd like to ask you another question.
> ;) If you'd rather not, just reply and saying I'd best sent this to
> the list. I'll fully understand.

Not a problem. I will embarrass you by cross-posting to this list,
though, since other people may find this helpful. :-)

> Essentially, I recently discovered how to do many-many relationships
> using a third table between the parent and child tables that stores
> parent_id and child_id to establish a relationship. From what I
> understand this is quite a common practice and possibly the only means
> to creating many-many relationships.

Yep. In the real sense, there is no many-to-many relationship, it's
just these two cooperating one-to-many relationships.

> What I'm stuck on is how, if possible at all, to make the database
> manage the referential integrity between these three tables. ie:
> deleting a parent row, the DBMS removes related row in the relations
> table and IF there are no relations rows left which point to the child
> row in the child table - then delete that row also.

To make sure I understand:

Class : holds information about classes being taught
    classid
    classdate
    title
    room

Student : hold information about students
    studentid
    name
    phone

ClassStudent : which student takes which class
    classid
    studentid
    regdate

So, if a Class or Student is deleted, you want the related
StudentClass records deleted:

CREATE TABLE ClassStudent (
    classid int NOT NULL
        REFERENCES Class
        ON DELETE CASCADE,
    studentid int NOT NULL
        REFERENCES Student
        ON DELETE CASCADE,
    PRIMARY KEY (classid, studentid)
);

It sounds like you want, if a class is deleted, delete all
StudentClass.*In addition*, if there are no StudentClass records for
that Student, delete it also.

If I'm understanding this correctly, I'd handle this w/a trigger on
DELETEs to ClassStudent--if deleting a ClassStudent would leave no
parents, get rid of the parent.

> And going in the other direction. When time comes to add a child
row,
> can the DBMS be setup to manage the necessary relations rows
and
> parent rows? I'd imagine I would need triggers to accomplish this -
as
>  I don't think there is such a thing as a CASCADE INSERT :)

There is no CASCADE INSERT. The rules for INSERT are
straightforward. You can't add a StudentClass if there isn't a
referenced Class and a referenced Student (pending DEFERRED; see
the draft tutorial for info on that.)

If that's not what you want, what exactly do you want to manage
about the relationship?

> Lastly, is it a wise design choice to take this so far in the DBMS, or
> would I be better of (in general as I realize each case is probably
> unique) to handle this within my code, in this case PHP.

Personally, I'd say, when practical, let the database handle the
thinking around the data. When you convert those PHP scripts to
Python or Perl, you'll thank me. (Or, when you edit the data using
pgaccess and forget to do the trigger stuff manually, or when you
hook it up to a ODBC frontend client. Or.... :-) )

--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)