[Catalyst] update record with unique ID

[Danny Warren]

Christopher H. Laco wrote:
> Michael Higgins wrote:
>> Hello, Cat-list-ers,
>>
>> Here's a pared-down version of my question, hoping it's an easy one:
>>
>> ERROR:
>> "DBIx::Class::ResultSet::update(): Error executing 'UPDATE ...
>> Duplicate entry '140949' for key 1 at 
>>
>> QUESTION:
>> How do we specify UPDATE ... blah WHERE (ID='UNIQUE')
>>
>> CODE:
>>     my $result = [$c->model('WhateverDB::Whatever')->update({id => $id},{
>> this=>that,
>> another=>something,
>>         });]
>>
>> How to update with the unique column... or do I just have to delete the old
>> record and insert a new one from scratch?
>>
>> TIA,
>>
> 
> 
> Does not compute.
> 
> First, I'm going to assume id is your primary key. Why would you want to
> change that?
> 
> Second, based on the error you've provided you already have another
> record with the same $id you're trying to update this record to...
> 
> Aside from that, conceptually, you want to find, then alter something...
> 
>     ->find($oldid)->update({id => $newid});
> 
> Whether the db/dbix will alow you to move the primary key like that, I
> can't say. I generally try to avoid evil magic.
> 
> -=Chris

Agreed.  Scary voodoo.

DBIC (and your database itself) should have NO problems updating a 
regular column that has a unique constraint, as long as the data is 
actually unique.

If your "id" column isn't actually a primary key, and you just happened 
to name a column "id" in your database by accident, then this might 
explain your surprise at the behavior.  Depending on how you set up your 
model (like if you included PK::Auto in your DBIC::Schema setup) then 
you might be treating this column like a primary key when you didn't 
really mean to.

If you *did* mean to go about mucking with the primary keys: don't. 
Think of primary keys as useless magic random values that only the 
database cares about, and do not expose them to end users wherever 
possible and do not assign bizlogic to them.

If you have some sort of "id" data (like a "product id" or an "account 
id") - anything that a user would see or edit - make a new column for it 
with a unique constraint.  Expose that to users and let them edit that 
all they want.  If you let the users change the primary keys, all sorts 
of bad things may happen with the relationships in your db.

Plus, visiting a url like /products/SD34234/edit is a lot nicer for end 
users and security than /products/34/edit (since "34" is totally 
meaningless, whereas product "DF45234" actually makes sense to an end user).

You should also be able to run a "find" on your non primary key column 
as long as it is declared unique, so there is no reason not to do this.

Danny