Well if someone attempted that, they would get a primary key contraint violation. This is not a very friendy error message, but you can always create your own error template to be defined in CF Admin and make it alot more friendly to the user. It would look like so without a user friendly template. AS you can see below, this also maintains integrity for iserting duplicates where you do not want them. I think it would be much faster for you to create the relationships on your tables then it would be for you to do checks against the DB. This would be a one time thing whereas checking it on the front end would be every update or insert or delete that you do.
Error Diagnostic Information ODBC Error Code = 23000 (Integrity constraint violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_components'. Cannot insert duplicate key in object 'components'. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:56) in the template file C:\CONSOLE\_ACT\ACT_ADD_COMPONENT.CFM. Date/Time: 01/09/02 10:16:01 Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0) Remote Address: 66.27.3.82 HTTP Referrer: http://www.carnivorepc.com/console/index.cfm?fuseaction=componentInsert Query String: fuseaction=componentInsert There are two major products that come out of Berkeley: LSD and [Unix] BSD. We don't believe this to be a coincidence. Doug Brown ----- Original Message ----- From: "Gyrus" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, January 09, 2002 9:49 AM 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
