Christian Buettner (4/16/2009)
Jack Corbett (4/16/2009)
Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.Never thought I would ever have a different opinion than you, but never say never.
I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition 😀
(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)
Bob Hovious (4/16/2009)
Somehow users managed to input some of the customer order numbers as lower case
Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database.
I figured I'd hit both of these in one post.
Christian,
I'm not sure we disagree. The point I was trying to make, and obviously not clearly, is that a business rule should be enforced in the application so that you do not "waste" a round-trip to the DB with invalid data. Would I want to put some kind of validation in the database, too? Yes. I'd probably use UPPER in my insert/update procedures. If you aren't using stored procedures, but an ORM tool or ad-hoc SQL, you'd have to use an INSTEAD OF trigger in the database to make sure the data was inserted properly and apply the UPPER, otherwise it still has to be done in the application. Ideally you would use a case-sensitive collation for the column and a check constraint.
Bob,
I missed the part of the post you quoted, which causes us to assume that the application IS supposed to be validating the data. As I said above, that doesn't mean I wouldn't validate in the database as well, mainly to protect the database from some ad hoc update by me or another DBA. Is there an upper case only collation? I know you can do case-sensitive, but didn't know you could require one case.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question