Forum: Ruby on Rails Why can't I change value of the primary key?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Mike B. (Guest)
on 2006-05-17 16:26
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.
Jón B. (Guest)
on 2006-05-17 16:36
(Received via mailing list)
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 B. <removed_email_address@domain.invalid> wrote:
>     end
> What's going on here?
>
> Thanks.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


--
Bill W. (Guest)
on 2006-05-17 17:53
(Received via mailing list)
Hi Mike,

Mike B. 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
Thibaut B. (Guest)
on 2006-05-17 17:59
(Received via mailing list)
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
Mike B. (Guest)
on 2006-05-17 18:10
> 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 B. (Guest)
on 2006-05-17 18:29
(Received via mailing list)
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 B. (Guest)
on 2006-05-17 20:52
Stephen B. 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 D. 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
Curtis S. (Guest)
on 2006-05-17 21:54
(Received via mailing list)
On 5/17/06, Mike B. <removed_email_address@domain.invalid> wrote:
> Stephen B. 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.  :)
This topic is locked and can not be replied to.