How to update a primary key

Question

How to update a primary key

Dylan Anderson Apr '14

Created Apr '14	Last Reply Apr '14	Replies 1	Views 1040	Votes 0

Dylan Anderson 125.8k

Apr '14

I'm trying to anonymize my user records - and part of that process is to change the username to an anonymous string. The username, however, is the primary key of the users table. So, this code:

$new_username     = $this->getAnonymousUsername();

$this->username   = $new_username;
$this->password   = $new_username;
$this->email      = '';
$this->first_name = 'Anonymous';
$this->last_name  = 'Record';

$this->save();

gives me this query:

UPDATE 
    `user` 
SET
    `password` = '__anon__d9e0fb0f0da033e2eeb6898b86db7787', 
    `source` = '', 
    `email` = '', 
    `first_name` = 'Anonymous', 
    `last_name` = 'Record', 
    `role` = 'Student' 
WHERE
    `username` = '__anon__d9e0fb0f0da033e2eeb6898b86db7787'

Is there anything I can do without resorting to PHQL (which is fine if that's what I have to do), to get the username to update?

Simon Paterson · Answer 1 · 2014-04-29T21:06:55-07:00

It might be better to change the structure of your database.

In my experience, primary keys cause fewer issues when they don't need to change. I use a simple auto-incrementing integer as the primary key, and set fields like email and username to have the unique attribute, rather set them to be primary keys. This allows users to change email and username without effecting the primary key, while still preventing other rows from containing the same value in that field. This approach works well for me.

Dylan Anderson · Answer 2 · 2014-04-30T09:13:01-07:00

@BhSimon - I see your point. For 99.9% of my application, the username is immutable, so having the username as the primary key isn't an issue. This anonymization process is a behind-the-scenes administrative function that happens after the user accounts are no longer being used. So while your point is a good one, it would be much less work to resort to PHQL (if I have to) for this one purpose, than redesign my database. Thanks nonetheless.