# Why can't I change value of the primary key?

**URL:** <https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783>\
**Category:** Rails\
**Created:** [May 17, 2006, 2:26pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783 "2006-05-17T14:26:17Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 2:26pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/1 "2006-05-17T14:26:17Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 2:36pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/2 "2006-05-17T14:36:00Z")

</div>

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](mailto:removed_email_address@domain.invalid) wrote:

> ```
> end
> 
> ```
> 
> What’s going on here?
> 
> Thanks.
> 
> –  
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

–

---

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 3:53pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/3 "2006-05-17T15:53:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 3:59pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/4 "2006-05-17T15:59:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 4:29pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/5 "2006-05-17T16:29:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 6:52pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/6 "2006-05-17T18:52:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 7:54pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/7 "2006-05-17T19:54:45Z")

</div>

On 5/17/06, Mike B. [removed\_email\_address@domain.invalid](mailto: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. 🙂

---

<div class="post-metadata">

**Author:** ![asplake](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/2bfe46/32.png) [@asplake](https://www.ruby-forum.com/u/asplake)\
**Post date:** [May 17, 2006, 4:10pm UTC](https://www.ruby-forum.com/t/why-cant-i-change-value-of-the-primary-key/60783/8 "2006-05-17T16:10:47Z")

</div>

> 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.