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

Rails
1

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.

2

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. [email protected] wrote:

end

What’s going on here?

Thanks.

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

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

–

3

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

4

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

5

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

6

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

7

On 5/17/06, Mike B. [email protected] 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. :slight_smile: 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. :slight_smile:

8

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.