Hi Alan,
Maybe you could CC your mail to pgsql-general so that anyone can participate.
> When building referential integrity, Foxpro has a
> "restrict" option which can be placed on the
> parent-to-child delete event. The meaning here is to
> prevent deletion of a parent record when it has
> children in a relationship. I'm not sure how to
> implement this in postgre other then writing the
> front-end application VERY CAREFULLY so as to avoid
> deleting a parent when it has children. (Having the
> child be deleted when the parent is deleted makes
> sense from a referential point of view, but since I am
> dealing with a medical database, I just assume my
> staff didn't remove records after making a data entry
> mistake.) Moreover, the postgre trigger of cascading a
> delete is going to cause problems with at least one of
> my tables. In designing this table, there are two
> fields that reference two parent tables such that if
> one of the parents is deleted forcing a delete cascade
> into this file, it will remove the detail record (in
> this file) also needed for the second parent. Hence,
> referential integrity is violated. A restrict clause
> for these parents would avoid the problem.
There are several ways to achieve this in PostgreSQL :
- foreign keys (this is what it is for. foreign keys are specific rules),
- write custom rules (rules differ from triggers as they can re-write SQL
during parsing, i.e. stop an SQL query from being executed),
- writte triggers that test children existence on delete.
As for me, I use triggers because before 7.2 it was impossible to drop rules.
From 7.2+ on, the best solution to test parent-child relashionship are rules.
This can be done quite easily within pgAdmin2.
> A second problem which I have partially solved
> (but don't like), is that within a table I need to
> make sure that records are unique when they are built
> upon user entry data. Specifically, I don't wont to
> trust the front end application to correctly input
> "wayne" or "Wayne" (etc.) as "WAYNE". My solution,
> which seems cumbersome, is to make a unique index in
> postgre based upon my added function of "namebday"
> which combines the first and last names input with the
> birthdate into a string. The names are forced to
> uppercase with the string returned as text for the
> unique index. It would be nice to force an unique
> index with simple syntax like....create unique index
> ...on zPeople (cLastname,cFirstname,dBirthdate). I'm
> not sure exactly what that type of index would produce
> nor am I sure it would stop "Wayne" from being added
> when "WAYNE" is already present. Ofcouse using
> (upper(cLastname),upper(cFirstname),dBirthdate) as the
> argument crashes. (So I ended up writing my "namebday"
> function.)
This is a nice solution but it will slow down your database (in the case of a
large database). Create a field name_tg and store upper(name) in name_tg
using a trigger. Add a unique index on name_tg.
Do not hesitate to use pgAdmin2 to write rules and triggers. Only the last
CVS version supports trigger pseudo modification (=fake drop / create to
simulate trigger modification). After installing pgAdmin2, the lastest
binaries can be downloaded from
http://cvs.pgadmin.org/cgi-bin/viewcvs.cgi/binaries/ (all binaries are needed
when upgrading).
Have fun,
Jean-Michel
