On Jul 16, 2007, at 3:29 PM, Perry Smith wrote:
On Jul 16, 2007, at 3:08 PM, Gregory Stark wrote:
"Perry Smith" <pedz@xxxxxxxxxxxxxxxx> writes:
Right now, it would be nice if I could get a check constraint to
be deferred.
Its a long story. I want a circular constraint. The way things
are set up
right now, it would be easy if I could defer my check
constraint. I'm doing a
polymorphic relation. One direction is a simple reference a
fixed table. The
other direction is a reference to table that changes based upon
the type of
the item. I can do this check in a function which implies it is
a check
constraint.
The main problem with this is that check constraints which refer
to other
tables don't really work. Not to the degree of rigour that
referential
integrity checks maintain.
Consider what happens if someone updates the record you're
targeting but
hasn't committed yet. Your check constraint will see the old
version and pass
even though it really shouldn't. It'll even pass if the update has
committed
but your query started before it did so.
This brings up a point that I have wondered about. I think I need
a nice clear concise explanation of how the magic of a relational
database transactions are done.
I'll go see if I can find one. If anyone has a pointer to one,
that will help me the most right now.
The postgres docs are great: http://www.postgresql.org/docs/8.2/
interactive/mvcc.html
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com