From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Alan Wayne <alanjwayne(at)yahoo(dot)com> |
Cc: | Jean-Michel POURE <jm(dot)poure(at)freesurf(dot)fr>, pgsql General List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [ODBC] ODBC |
Date: | 2002-03-17 19:13:03 |
Message-ID: | 20020317110619.O82906-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-03-17 21:37:19 | Re: [ODBC] ODBC |
Previous Message | Tom Lane | 2002-03-17 19:13:01 | Re: Maintainer(s) for gborg? |