> 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:236780 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
