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.
on 2006-05-17 16:26
on 2006-05-17 16:36
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@example.com> wrote: > end > What's going on here? > > Thanks. > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > firstname.lastname@example.org > http://lists.rubyonrails.org/mailman/listinfo/rails > --
on 2006-05-17 17:53
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
on 2006-05-17 17:59
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
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.
on 2006-05-17 18:29
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
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
on 2006-05-17 21:54
On 5/17/06, Mike B. <email@example.com> 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. :)