Apply UUID function to database field from PHP

Question

Hi all,

I have this CodeIgniter and MySQL application.

I have a database table where one field must be unique. When I "delete" a record from the table I don't want to really remove it from the table, I just want to set free the value of the unique field so it can be used for a future new record without conflicts and leave it there.

At first I tought applying some sort of UUID function to the field would be a good solution. Can somebody please point me how can I apply the UUID function to the field from the PHP code? I googled about it and couldn't come up with nothing, CodeIgniter's docs neither.

Some other toughts are also welcome and appreciated.

Thanks in advanced!

Answer 1

If I understand correctly your aim here, you can do this with a single line of sql statement.

update users set username = CONCAT(UUID(), username) where username = "username_to_be_deleted"

This is quite a good attempt to keep the unique constraint, unless some wicked handed user of yours picked a username that is in the format of a unique id + some string, and it will accidentaly match. Not likely, though.

Added benefit: as UUID has a fixed format, you can always extract the original username from the encoded value.

And of course, a much better aproach, if you do not add a unique constraint on a field like this, but rather enforce uniqueness programmatically.