There would be an error, but IMHO, *this is a good thing* - during development, it would illustrate an area where data integrity was being violated, and allow you to fix the problem (cascading deletes through triggers, or done manually through cfml)
----- Original Message ----- From: "Gyrus" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, January 09, 2002 11: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 > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc 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
