Currently its not possible to update a primary key via active record.
Because the update function uses this the primary key value directly from the attributes via the function $this->getPrimaryKey(), which aren’t the original from the database if changed via a update form.
I would use add a private Property to store the primary key after load/find and use this stored value(s) for the update instead of the key values from the attributes.
Then we can easily add the primary key fields to the safe attributes (if wanted) and can edit and change the values.
the way i am currently changing my primary key values is directly in the controller:
But this isn’t that “good”, cause I don’t call any function which normally called in save() e.g. after save etc. So if i add something to e.g. after change I also have to remember to call this function in the the code part above.
There is nothing really special about it. I have a varchar(9) primary key which is some kind of project short name e.g. P1204 and some other without any numbers.
The main problem is, this isn’t my database (so I cannot change anything) I am working with and those key’s are used in several relation tables.
I also see nothing really "special" on a unique varchar primary key which is also set to be updated on cascade with an InnoDB database. Not everyone uses a autoincrement key and some varchar key is sometimes better readable when you look directly into the tabledata. I guess the old developer was just too lazy to do some queries when displaying the relation data.
The problem is, a wrong sql update command can be created if I add the primary key to the safe attributes.
As its a “normal column” why shouldn’t I also be able to change it?
I mean cause its a hand inserted "text" there could be any cause for a change of this key. Beginning from an easy mistyped on insert to an change of the project (in my case).
Sadly I just have to do it, my own database schema wouldn’t also use varchars as keys.
I think this is a very special case that should not be handled by AR. As the SQL for updates on tables without Pk depend a lot on the table structure you will always end up with custom SQL. How else would you make sure that the row that you want to update is uniquely identified?
But you still can enhance your ARs with some custom methods. They can use DAO to perform these updates according to your logic. Something like this:
public function updatePk()
{
$command=$this->db->createCommand('UPDATE bla SET id=:id WHERE colA=:a AND colB=:b');
$command->bindValue(':a', $this->a);
$command->bindValue(':b', $this-><img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />;
$command->bindValue(':id', $this->createNewPk());
$command->execute();
}
public function rules()
{
return array(
array('ID', 'unique'),
);
}
Solves the problem with uniqueness. On validate() fails if the new ID is already used.
The only thing that has to be changed is store the original key on Load/Find. And use this stored key for the update! And not the one from the attributes. Because you would also have the same problem "wrong sql" when you set the model->primary key attribute by hand and try to save afterwards.
My code from above works, its just not that nice. And I think it should be changed in the yii framework to be able to change the primary key as well.
I mean currently it gets the key from the attributes, so it can also get the keys from some private property which is only filled on load of an entry, same as the direct attribute, only just not accessible and changeable from the outside.
To make sure that you understand where my code from the 1st post is used I added some more code to the 1st post (hopefully right because I just typed it, but its normal crud code)
the only problem is really that i get
UPDATE tbproject SET ID=‘mynewkey’, … other columns… WHERE tbproject.ID=‘mynewkey’
instead of
UPDATE tbproject SET ID=‘mynewkey’, … other columns… WHERE tbproject.ID=‘myloadedkey’
when I try to edit the ID, which is just added to save attributes when calling $model->save(). If I leave the ID unchanged the save() works as it should, but on change of the ID it will use the new submitted ID for both new key value as well as the key to change, which is wrong!
I personally like "natural" primary keys much more than those automatically incremented IDs. I feel like introducing such a generated ID is more like a very common and widely accepted workaround.
Granted, it has its positive aspects. It never has to be changed, it is a general approach that fits every table, you don’t need to worry upon updates and foreign keys (if it really won’t be changed), and so on…
But on the other hand, if you do proper analysis of the data you have to store, you will (in most cases) find a real, a natural identifier of your data that makes every sort of generated ID redundant. That real Pk will describe your dataset in a more precise way than an id could do, thus making it easier to “read” tables if you ever need to look at them (and while developing, I do it again and again). You don’t blow up your database with unnecessary integer values (all you need those IDs for is for referencing your data, never for the data itself).
Of course one can argue that it is hard to find “real” identifiers for real world data. Users for example might be identified by their names. But we all know that there are many people that share the same name. So this might not be sufficient. But as soon as you introduce an unique column (maybe because you don’t want users to share the same nick because that could confuse the community - only an example) you found the column that can serve as primary key. There’s no more need for any strange numbers in your tables.
That’s why I highly appreciate this feature request.
totally agree on that, and as I started using yii for the first time I stumbled on this problems, so maybe this feature request should have the highest priority, IMHO of course!
I’ve overcome this issue quite simply by making the following changes:
class MyModel extends CActiveRecord
{
private $_originalKey;
public function afterFind()
{
$this->_originalKey = parent::getPrimaryKey();
parent::afterFind();
}
public function getPrimaryKey()
{
return $this->_originalKey;
}
}
The CUniqueValidator must also be changed like so:
I haven’t fully explored the else statement after this, but I’ll post any updates if I run into issues.
A lot of implementation issues around keys can be resolved quite simply by removing the special handling for primary keys, and treating them as any other key or index. The onus is then on the application to ensure that when you’re updating or deleting that you’re doing it to the subset (or individual record) you want.
