Re: Databases for Linux

I'm looking to join the camp of people who believe data logic should be
handled within the database(postgreSQL) and not the client code(php). I
realize there are pros and cons to this approach but its something I want
to explore and be able to take advantage of where applicable. However most
of the elements involved in doing this I know little about, ie: stored
procedures, triggers, referential integrity, constraints, etc.

One con, I understand, would be ability to migrate to another DBMS. If I
were to use PL/pgSQL for my stored procedures - is this language a standard
across DBMS? I'm not expecting this since most DBMS can't seem to follow
SQL standards with simple things like dates or wild-cards.

. . . .

Another, more specific question: I'd like to know how to make two tables in
PostgreSQL with a relationship between them so that when the parent row is
removed from one table, its children in the other table will also be
removed by PostgreSQL. I don't even know how to specify the foreign key. :)
Conceptually I understand (I believe). I'm just no sure how to define this
within PostgreSQL

I've done some searching through the on-line postgreSQL manual - in hopes
to find a chapter in it akin to "referential integrity", but no such luck.
Its possible I'm confusing the terminology.

So, if someone can point me towards some additional information on this,
that'd be appreciated.

. . . .

Furthermore, my need for information on this topic will obviously goes
beyond this one issue - can someone recommend either an on-line resource to
advanced data modelling and / or database design or books worth purchasing
on the topic.

Summary of questions:

1) How cross database is PL/pgSQL?
2) How to define foreign -> primary key relationship where foreign key
records are deleted
3) recommended readings on any of the above.

- - - - - - -
- - - - -
WARNING: Some experts believe that use of any keyboard may cause serious
injury.
Consult Users Guide.
dfunct@telus.net

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

On 8 Dec 2000, at 13:35, Soma Interesting wrote:

One con, I understand, would be ability to migrate to another DBMS. If
I were to use PL/pgSQL for my stored procedures - is this language a
standard across DBMS? I'm not expecting this since most DBMS can't
seem to follow SQL standards with simple things like dates or
wild-cards.

Oracle's PLSQL is very similar; Interbase's procedural language is
similar, but requires some rewriting. MS Access doesn't have
procedural languages (you can use VBA in forms, reports, but not
table operations.) MySQL does not have at all.

Or, of course, you could use plperl or pltcl for your PG procedural
stuff; I doubt other databases offer these as PLs, but, the code
(w/modifications) could live outside the server in some cases.

Another, more specific question: I'd like to know how to make two
tables in PostgreSQL with a relationship between them so that when the
parent row is removed from one table, its children in the other table
will also be removed by PostgreSQL. I don't even know how to specify
the foreign key. :) Conceptually I understand (I believe). I'm just no
sure how to define this within PostgreSQL

CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id REFERENCES parent ON DELETE CASCADE);

I've done some searching through the on-line postgreSQL manual -

in

hopes to find a chapter in it akin to "referential integrity", but no
such luck. Its possible I'm confusing the terminology.

I recently wrote a (draft) tutorial on referential integrity. If you go
to the PostgreSQL website & dig into the archives of the pgsql-doc
discussion list, you'll see it there from about 5 days ago.

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

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)

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.