Re: [ODBC] ODBC

On Sun, 17 Mar 2002, Jean-Michel POURE wrote:

> 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),

The above sounds like a classic foreign key.  The default is a restriction
to the delete when children are present.  You need to add a clause to make
cascaded deletes.

> - write custom rules (rules differ from triggers as they can re-write SQL
> during parsing, i.e. stop an SQL query from being executed),

Before triggers can stop updates and such for any of the affected
records by returning NULL as well I believe.

> > � � 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.

Or if you don't care about getting back the original case you don't even
need another field. This should be a fairly simple before trigger.