Re: pro- foreign key propaganda?

On May 15, 12:28 pm, "sinister" <sinis..._at_nospam.invalid> wrote:
> "Bob Badour" <bbad..._at_pei.sympatico.ca> wrote in message
>
> news:482c3e85$0$4046$9a566e8b_at_news.aliant.net...
>
>
>
> > sinister wrote:
> >> I'm supervising the development of a database system with a web
> >> interface.
>
> >> 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?

Both. I say from experience.

Marshall Received on Fri May 16 2008 - 16:36:53 CEST