I know you're probably getting a lot of information here, but... Why add extra code to your app when you can let the db do it's job. I'm a firm believer in letting the different parts of an application (i.e. db, cf, com, java, etc.) do it's particular job that it was intended to do. In addition, you do need to wrap these different parts of your application in cftry/cfcatch blocks to catch any exceptions those third-party calls may generate.
I'm not sure what kind of message might be returned from a db if a check constraint error occurs, but most do return a specific error number and diagnostic information which you can catch in cf and handle appropriately. More elaberate systems can catch particular errors and fix them on the fly so the user never knows what happened. In a nutshell, let the db do its job. in the end, you'll have cleaner code thats scalable, flexible and easy to maintain. Mark -----Original Message----- From: Gyrus [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 12:50 PM To: CF-Talk Subject: Re: Defining internal DB relationships necessary? > Well here is my thought. As far as my DB is concerned I fully use > creating a pri key and foreign key relationships where needed on all my > tables. This helps so much in avoiding someone delteting something from > one table that relies on something in another table. As I said, I do rigorous checks in the CF app for this sort of stuff. I think my initial reasoning was, I'd like to control error-checking at the app level. So, if someone tried to delete a record whose PK is related to several FK's in other tables, I'd do a query to check, and tell them why they can't delete. I just thought, if I rely on the DBMS, it might prevent the deletion, but the page might break and I don't know what sort of error message they'd get, whether it would be friendly or not. What *would* happen if such a delete was attempted on a DB that had rigorous relationships defined, but where the app had no checks? What would the user see in the return page? Would it break the generated CF? Or would the "success" page show, despite the fact that the DELETE was rolled back (with no indication that it had failed)? I know this is what <CFTRY> is for, but I just opted to spend time coding app-based checks rather than defining relationships and coding error handling. Anyways, isn't <CFTRY> kind of generic, as in, you could catch DB errors but not really know what's gone wrong? > Another reason for > doing this is that I use SQL 2K and use the diagram feature alot since I > have several tables and I can see how each table relates to the others > in the schema, as you mentioned. Yeah, some Access DB's I've done *do* have relationships defined, because I've defined them so I can visualise them, or need to present a design to a client. I actually came to this question because I'm building a developer toolkit in my new backend CMS template, which contains a pretty basic system for creating and altering DB tables online. It's become a lot more complex than I intended, but I am giving the option to index specific fields, requiring default value if NULL not allowed, forcing definition of PK field on creation of new table, etc. I just looked at some sample SQL in 'SQL in a Nutshell' and wondered about spending another few nights coding all the constraints code for SQL definition of relationships... :-| - Gyrus ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - [EMAIL PROTECTED] work: http://www.tengai.co.uk play: http://www.norlonto.net - PGP key available ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
