Hello.
On Fri 2002-06-21 at 10:47:28 -0400, [EMAIL PROTECTED] wrote:
> On Thursday 20 June 2002 21:15, Benjamin Pflugmann wrote:
>
> > IMHO, the interesting question is: how could the tables get out of
> > sync, in a way that foreign keys would be able to prevent? I am not
> > able to see a way in everyday work (explicitly filling in nonsense
> > does not count).
> >
> > The only thing I can see that could happen in normal use is that you
> > get an entry for orders but none in orderItems, because a program is
> > interrupted. That could happen with foreign keys as well.
> >
> Well, there are a couple of considerations. When you simply consider
> something simple like you have a webapp and it manipulates a database, then
> its probably not too much of a concern that things can get out of sync. The
> world is rarely that simple though. Over time its quite possible other bits
> of code will be written for various reasons which access the same data, for
> purposes of archiving, reporting, order fulfillment, etc. Some of that code
> is bound to monkey with the database, and all it takes is one bad piece of
> code to botch things up.
>
> Aside from using key constraints, the other strategy is to use transactions.
Yes, I absolutely agree, transaction would help. But we were talking
about foreign keys.
[...]
> While, as the previous poster suggested, you can use some sort of flag, this
> has several implications and doesn't entirely solve the problem. 1st of all
> it doesn't prevent an order from never being flagged as complete when it
> should be.
Neither do foreign keys.
> 2nd the code is less certain to behave properly in the case where an
> insert of one item in an order fails but others don't.
How would foreign keys help? All items refer to the order, which
already has been inserted. So the foreign keys constraint is fulfilled
even if some of the item inserts fail.
> A slight error in the code could lead to half an order in this case
> once again.
Same with foreign keys.
The only thing foreign keys can prevent, is that someone writes a
sloppy program where a items refers to a non-existing order. But then,
there are so many ways how the program can mix up, that I would not
rely on them (e.g. use always "1" as orderid and foreign keys will be
happy - as there is quite probably an order "1").
> Given that you don't have transactions or foreign keys available at
> this time you might want to do as suggested, but I'd upgrade to a
> version of MySQL that does support them as soon as possible.
If you meant to say, using transaction would have been the Right
proposal, instead of a "state" column, I agree. But, then, you
just answered yourself why I did not.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
