Thanks for the explanation, Isaac! According to your explanation, I actually did have a problem with referential integrity.
I had been manipulating the data in the database manually over several weeks working on the queries for output and had not built in the safeguards to prevent destroyed referential integrity. I was so pre-occuped with the output display that I built that first, instead of the code that would manage the data, including referential integrity safeguards. It's a good lesson I learned. I usually build the basic code handling sections first and handle adding, updating, and deleting data through those sections to test the queries and displays, but this time jumped the gun and went straight to the queries and displays and just handled the data manually. I'll just have to make sure I follow the "good" if not "best" practice of building the data manipulating sections first and use those to deal with the database instead of putting my little fingers in there directly. Over the course of several weeks, I corrupted the data. Thanks again, Rick -----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Saturday, April 01, 2006 9:13 PM To: CF-Talk Subject: RE: Anything inherently wrong with this query? > The first...bad data...I had previously entered data > for another test run of this application and, I guess, > somewhere along the way I deleted some of the data > from some of the tables, so the data lacked > referential integrity. (I think that's the right term...) That depends on the state the database was in. Just an FYI for clarification: lacking referential integrity means there are "orphans" in the data. I'm sure you've guessed an orphan is a record which contains a column value (foreign key) which is supposed to match a value in another table's identifying column (primary key) but which contains a value which is not found in that column. If that state didn't exist, for instance if the missing data had been removed from a foreign key table (a "child" table or a cross-reference table) then there is still referential integrity. The trick of course is to maintain referential integrity and if you do that, then you only ever have to worry about having missing data, not having orphaned data (which takes up space in the database needlessly and slows down queries). Foreign key constraints are designed to produce an error in any condition in which referential integrity would otherwise be violated, thus they prevent the deletion of data which would produce orphaned data and thereby maintain referential integrity in your database. Here's a pseudocode illustration: CREATE TABLE PARENT_TABLE ( parentid varchar(35) primary key, parentname varchar(100) ) CREATE TABLE CHILD_TABLE ( childid varchar(35) primary key, parentid varchar(35) references PARENT_TABLE(parentid), childname varchar(100) ) This will create a foreign key constraint between the parentid column of the child table and the parentid column of the parent table. Now, lets assume we add some data: PARENT_TABLE parentid: 'p1' parentname: 'Rick Faircloth' CHILD_TABLE childid: 'c1' parentid: 'p1' childname: 'Reginald Faircloth' Now, if you were to delete the record for the child Reginald, your database would still have referential integrity, even if that record should not have been deleted. If on the other hand you were to remove the foreign key constraint and delete Rick without first deleting Reginald, you would no longer have referential integrity because the parentid 'p1' in the child record for Reginald would no longer match any of the records in the parent table. The foreign key constraint prevents you from losing referential integrity in this case by producing an error if you attempt to delete Rick without first deleting Reginald. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236781 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
