Sam Mason <s...@samason.me.uk> wrote: > On Wed, Aug 05, 2009 at 01:29:13PM +0200, Pavel Stehule wrote: >> When you have a full set of constraint, then you don't need to >> validate input. Just you will execute statement. > > Constraints are different from input validation as they rely on > state that the database's client by definition doesn't have > (otherwise it would be able to do the constraint checking just as > well as the database). Just because something *can* also be checked within the front end doesn't mean it's best if it *is* checked there. When we were using a client/server model, we couldn't put the validations just in the client software, because there might be many places which could cause a violation of the business rule, and it was not reliable to count on all programmers knowing every rule and where it would need to be enforced. On top of that, there are cases where data is modified outside of the normal application software, and constraints only enforced in the application obviously provide no protection for data integrity in those cases. Attempting to put enforcement just in the RDBMS layer was tricky, though, because the messages tend to be written from the perspective of a database hacker, and tended to confuse or frighten the less computer-savvy staff using the software. I won't get into the all details of how we've dealt with this; primarily I want to chime in that it is a real problem. Briefly, though, our solution in the multi-tier environment did involve creating the ability to associate unique SQLSTATE values with failure of individual constraints for which there weren't well defined values (like there are for duplicate keys, for example). We could then have business write a friendly message for each such SQLSTATE. The more general ones were trickier, and I can say from experience that the ability to reliably pick off a table name or two when there's a duplicate key or a foreign key violation is critical to user-friendly behavior. Trying to enforce identical constraints in both the client code (for friendly behavior) and the database side (for better data integrity) is fraught with obvious problems. Anyway, the upshot is -- I think that it would be beneficial to allow, to the extent we can confirm it's not a violation of any applicable standard, a user-defined SQLSTATE to be associated with a constraint. I also think that it would be valuable to provide a mechanism for PostgreSQL-specific application code to be able to pick off one or two table names related to a "standard" constraint violation. I'm less convinced at the column or data value level, but I can see where it might be useful. Oh, and I've got nothing against XML as long as it's not exposed to a human being or application code. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
