I preferred the check constraint mainly because of the feedback "violation" messages. Other, more traditional constraints exist on other columns of the table. The names of those constraints contain information about the nature of the violation. I wanted to standardize the way I detected these violations and parse out the information. So I just added check constraints with names that followed the naming convention and always look for traditional constraint violation messages in my perl/DBI script.
No, no explicit locking, but as I said, the table being querried is static. -----Original Message----- From: Peter Geoghegan [mailto:pe...@2ndquadrant.com] Sent: Thursday, June 16, 2011 9:56 AM To: Gauthier, Dave Cc: Igor Neyman; Mike Christensen; pgsql-general@postgresql.org Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table? On 16 June 2011 14:41, Gauthier, Dave <dave.gauth...@intel.com> wrote: > I've dealt with something similar by using a check constraint and a stored > procedure. The check constraint calls a stored procedure, passing it (in > your case) the key you want to make sure doesn't exist in some other table. > The stored procedures queries that other table for the key and passes back a > YES/NO flag that the check constraint detects and acts on (constraint > violated or not). > > I'm not using this to check a prim/foreign key relationship for my app, and > the table that the stored procedure is querying is a ref table that is very > static. This approach may not be bullet proof for checking key relationships > in dynamic tables. I'll let others speak to that. Did you use explicit locking? If not, you likely have a race condition. The same applies to any sort of enforcement of business rules inside triggers (or, indeed, check constraints). Check constraints are generally intended to enforce simple, immutable rules (i.e. that only reference the tuple that the rule is enforced on). I would have used a trigger instead. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
