Rails - May 2006 - Why can''t I change value of the primary key?

One should be able to change the value of a primary key, right? But
it''s
not working for me. If I do this:

    @drug = Drug.find(tid1)
    @drug.id = tid2
    @drug.category = ''Test2''
    if @drug.save
        savedok = ''Saved successfully''
    else
        savedok = ''Not saved''
    end

I get ''Saved successfully'' but actually the record is not
saved or
changed at all, nor is there an error added to the object. If I comment 
out the second line, changing the id, the record _is_ updated with the 
new category name.

I''m using InnoDB in MySQL 3.23. There are no foreign key''s
defined.
Drugs has two "has many" relationships but no "belongs to"
ones. The
problem occurs with or without the "has many" relationships.

What''s going on here?

Thanks.

-- 
Posted via http://www.ruby-forum.com/.

Propably not possible without doing the SQL yourself. Changing the
primary key isn''t usually anything people want to do.

On 5/17/06, Mike Blyth <mike.blyth@sim.org> wrote:
> One should be able to change the value of a primary key, right? But
it''s
> not working for me. If I do this:
>
>     @drug = Drug.find(tid1)
>     @drug.id = tid2
>     @drug.category = ''Test2''
>     if @drug.save
>         savedok = ''Saved successfully''
>     else
>         savedok = ''Not saved''
>     end
>
> I get ''Saved successfully'' but actually the record is not
saved or
> changed at all, nor is there an error added to the object. If I comment
> out the second line, changing the id, the record _is_ updated with the
> new category name.
>
> I''m using InnoDB in MySQL 3.23. There are no foreign
key''s defined.
> Drugs has two "has many" relationships but no "belongs
to" ones. The
> problem occurs with or without the "has many" relationships.
>
> What''s going on here?
>
> Thanks.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

-- 
--------------
Jon Gretar Borgthorsson
http://www.jongretar.net/

Hi Mike,

Mike Blyth wrote:
> One should be able to change the value
> of a primary key, right?
Not in a relational data base.  A fundamental constraint under the 
relational model is that each record in a table must have a unique 
identifier.  That identifier is called the primary key.  Changing the 
primary key of a record "in place" is not allowed since it could lead
to a
violation of the "unique identifier" constraint.

hth,
Bill

Mike,

could you explain a bit more what is your final goal ? (ie: you wanted to
change the primary key for some reason, what is this reason ?)

regards

Thibaut
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060517/5ab5cbb3/attachment.html

> Not in a relational data base.  ...
> Changing the primary key of a record "in place" is not 
> allowed since it could lead to a violation of the 
> "unique identifier" constraint
Yes, in fact an error is generated by the object''s validation method if
I try to change it to an existing key. The "save" operation first 
validates the uniqueness of the key in any case. This would be no 
different than when the key is assigned when the record is first 
created. Changing a key to a different one would not violate the unique 
identifier condition as long as it was still unique.
> Propably not possible without doing the SQL yourself.
> Changing the primary key isn''t usually anything 
> people want to do.
I understand some of the arguments for and against using "meaningful" 
keys vs unchangeable ones used only to identify the record. If I was 
starting from scratch, I would probably use an auto-increment key and 
save the "meaningful" key in another field. But at this point that
would
be some trouble. If a "meaningful" key, such as in this case a patient
identification number, is entered incorrectly, it has to be corrected.

However, I read in a couple of places that there was nothing in Rails 
against changing the value of the primary key, the debate being simply 
whether it is desirable.

So, any other info on whether it is possible, or need to start with a 
new, meaningless key?

Thanks.



-- 
Posted via http://www.ruby-forum.com/.

I would argue that you *definately* shouldn''t change the primary key. 
For starters, if you have associated records, you would have to not only 
update the ''patients'' table but also all the associated
tables. Urg.

I guess your options are limited if you are working with an existing 
schema, but it really is worth considering other options as it may save 
you from a lot more hastle in the future.

If you *really* can''t see any way around it, you''d have to
remove any
auto incrementing from the db (as far as i''m aware, most rdbms wont let
you update an incrementing column) and then use sql manually:

mymodel.execute("update mymodels set id = 12345 where id = 123")

But again - imo this is asking to be whopped by the db design police...

Steve


Mike Blyth wrote:
>>Not in a relational data base.  ...
>>Changing the primary key of a record "in place" is not 
>>allowed since it could lead to a violation of the 
>>"unique identifier" constraint
> 
> 
> Yes, in fact an error is generated by the object''s validation
method if
> I try to change it to an existing key. The "save" operation first
> validates the uniqueness of the key in any case. This would be no 
> different than when the key is assigned when the record is first 
> created. Changing a key to a different one would not violate the unique 
> identifier condition as long as it was still unique.
> 
> 
>>Propably not possible without doing the SQL yourself.
>>Changing the primary key isn''t usually anything 
>>people want to do.
> 
> 
> I understand some of the arguments for and against using
"meaningful"
> keys vs unchangeable ones used only to identify the record. If I was 
> starting from scratch, I would probably use an auto-increment key and 
> save the "meaningful" key in another field. But at this point
that would
> be some trouble. If a "meaningful" key, such as in this case a
patient
> identification number, is entered incorrectly, it has to be corrected.
> 
> However, I read in a couple of places that there was nothing in Rails 
> against changing the value of the primary key, the debate being simply 
> whether it is desirable.
> 
> So, any other info on whether it is possible, or need to start with a 
> new, meaningless key?
> 
> Thanks.
> 
> 
>

Stephen Bartholomew wrote:
> you would have to not only
> update the ''patients'' table but also all the associated
tables. Urg.
But isn''t that why changes are automatically cascaded when there are 
defined relationships? But my experience and knowledge are limited to 
simple models, where if you change the parent key, you can simply 
cascade that change to all the tables  that reference that row.

I did try without autoincrement on the key, without success.

OK, for the record, I *do* understand why I "shouldn''t"
change the
primary key. (Though, interestingly, the discussion in 14.3 on pg 207-8 
of Agile Web Development does mention using meaningful keys as an 
option, and it would never make sense to do so if they couldn''t be 
updated at least to correct errors). Still, my simple application will 
work fine if I can do it and assuming that Rails or InnoDB does cascade 
the changes. If it''s not clean in Rails, though, I agree I''d
be better
off reorganizing the whole thing.

Thanks.

--Mike

-- 
Posted via http://www.ruby-forum.com/.

On 5/17/06, Mike Blyth <mike.blyth@sim.org> wrote:
> Stephen Bartholomew wrote:
>
> the changes. If it''s not clean in Rails, though, I agree
I''d be better
> off reorganizing the whole thing.
I would argue that you''d be better off reorganizing the whole thing
regardless...  You never know what the future may bring...  It''s best
to prepare for it now, while you''re doing the port.  If you know
it''s
broken, don''t call Band-Aid...fix it.  :)  It will be faster to do in
Rails than anything else...

Let Rails have its Id column and create an old_id, or legacy_id, or
customer_id, or whatever...and map it up.  :)

>Let Rails have its Id column and create an old_id, or legacy_id, or
>customer_id, or whatever...and map it up.  :)
Yes, I agree.

Just treat the old key as an attribute of your new rails model. You
can use ''validates_uniqueness_of'' to keep the old legacy key
unique.

Rails follows convention over configuration. You can venture ''off the
rails'', but you will have to carry more of the load yourself. Letting
rails do what it wants to do will probably work out as a simpler
solution for you in the long run.