Thread: RE: PL/pgSQL compatibility?

RE: PL/pgSQL compatibility?

From
Soma Interesting
Date:
>So, if someone can point me towards some additional information on this,
>that'd be appreciated.

Sorry, I'd forgotten to check that on-line book it has lots of the
information I was looking for.

http://www.postgresql.org/docs/aw_pgsql_book/aw_pgsql_book.pdf


Re: PL/pgSQL compatibility?

From
"Joel Burton"
Date:
> 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)

Re: PL/pgSQL compatibility?

From
Soma Interesting
Date:
At 06:29 PM 12/8/2000 -0500, you wrote:

You understood perfectly what I was trying to express and your answer
helped lots.

> > 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?

In the case of what I'm working on, I'll want to just add a row to
ClassStudent and it will automatically make corresponding semi-blank rows
in Student and Class based on the id's I inserted into ClassStudent. So I
would need to use a trigger for this also. I guess I was wondering if I
could get referential integrity to handle both the trigger you suggested
above and the trigger I'm suggesting - which isn't what referential
integrity is intended for.

In the context of what I'm working on, the related Student rows won't
always exist already and I'm trying to avoid writing PHP code to handle
Inserting a new class row, and inserting the user's requested number of
blank student rows, then inserting the corresponding rows in ClassStudent.

I can't really use a trigger because sometimes existing students will be
added to a class. In which case inserting the appropriate row would set off
the trigger, creating unwanted blank class and student rows.