On Mon, Oct 17, 2011 at 2:21 PM, Warren Young <mysqlpp@stripped> wrote:
> On 10/17/2011 1:35 PM, Adrian Cornish wrote:
>> Was wondering if you have any preferences on how to do the example
>> since REPLACE is going to require either a primary key or unique index
> Ah, yes, we've only been doing UPDATE...WHERE heretofore, haven't we?
> It wouldn't break my heart to have an AUTOINCREMENT id column in the stock
> table. It touches lots of things, but the only reason it doesn't have one
> yet is that there hasn't been a need.
> Does that really help, though? You don't want to hard-code IDs in your
> example. Your example could have two queries, one to pull records, then
> another to replace by some criterion, but then you're begging the question
> of why not use UPDATE...WHERE.
> Turning stock.item into a primary key might be better. Not really good DB
> design, but it's more appropriate for an example. I just worry that people
> will take it as DB design advice. If you go this way, it will let you
> replace rows by item name.
How about I add an auto_increment id to stock (for good DB design) and
also add a unique constraint on the 'item' column - this way the
example can use the unique constraint and now the stock table is a
little more modern?