Re: pro- foreign key propaganda?

From: Bob Badour <>
Date: Thu, 15 May 2008 17:53:13 -0300
Message-ID: <482ca2be$0$4047$>

sinister wrote:

> "Bob Badour" <> wrote in message 
> news:482c3e85$0$4046$

>>sinister wrote:
>>>I'm supervising the development of a database system with a web
>>>I had built the DB backend using postgresql. The guy we hired to take
>>>over the project from me---I have non-IT duties to attend to---for
>>>various reasons decided to switch to MySQL and created his own tables (as
>>>well as redoing the web interface).
>>>After a conversation involving two tables which I thought ought to be
>>>connected by a foreign key relationship, I went and checked his DB; I was
>>>worried that he didn't fully understand the importance of using foreign
>>>keys. So I ran mysqldump and then did "grep -i foreign dump.sql" and
>>>"grep -i references dump.sql." Nothing!
>>>So...sent him an email late in the day, stressing that it's important
>>>that foreign keys be used where possible, that as much of the model
>>>logic/business logic/whatever should be encoded in the database itself to
>>>ensure data integrity, and so on.
>>>What do I do if he gets back to me and says---as I'm sure everyone in his
>>>position does---"oh, but my PHP code makes sure everything is done
>>>right"? Not that I don't know what to say, but it would be good to have a
>>>pointer to an essay or webpage that succinctly explains why you're asking
>>>for trouble if you don't encode such relationships in the DB itself.
>>>Sure, I could say, "Your next assignment is to read a book on DB theory,
>>>and give me a 10 minute summary," but we don't have that much time...
>>If he is that much of a newb, make sure he is not using MyISAM. Maybe he
>>didn't declare any integrity because he doesn't think mysql will enforce
>>it in any case. But MyISAM can (will?) corrupt your data.
> I actually told him at the beginning to use InnoDB---because MyISAM doesn't 
> enforce fk constraints---and thankfully enough he did do that.
> MyISAM and corruption---due to lack of constraint enforcement, or because 
> it's buggy?


> In the previous version of the project we're working on, the guy (not the 
> same one) was using Microsoft Access.  A pretty important table got 
> corrupted, in the sense that for about 20% of the rows, half of the row 
> really belong with another row.  (Meaning, there's a name and a record 
> number assigned by another dept, and in those 20%, the rec num wasn't just 
> incorrect, it belonged to another name in our table, even though we have 
> about 2000 names, and the dept providing the rec num has at least tens of 
> thousands, so it can't be just wrong randomly.)  There was another data 
> source in the form of an Excel table (this is when I was trying to clean up 
> the data and put it in a nice PostgreSQL table), and they conflicted, and I 
> figured, "OK, Access isn't my favorite DB, but surely it must be because 
> someone took the Excel table and shifted some cells up and 'broke' the 
> rows."  Turns out Access was wrong!  I have no idea how _that_ happened. 

Using an end-user query tool as if it were a dbms is a recipe for disaster. Received on Thu May 15 2008 - 22:53:13 CEST

Original text of this message