Hello,

I'm trying to add a column to a table of mine to include a unique identifier using the mysql function UUID(). I tried this query to alter the table (that at the moment has about 8K records)

ALTER TABLE `users` ADD `uuid` VARCHAR( 50 ) NOT NULL DEFAULT 'UUID()' AFTER `id` ;

This query add the column, but sets the field with the default value of "UUID()" in all records, instead of the unique identifier for each of them.

I also tried this

ALTER TABLE `users` ADD `uuid` VARCHAR( 50 ) NOT NULL DEFAULT UUID() AFTER `id` ;

But I got a mysql error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UUID() AFTER id' at line 1

Is there a way I can set UUID() function as an default value for my table, so that all existing records get an UUID and new records too?

Thanks,

    Nope. As per http://dev.mysql.com/doc/refman/5.0/en/create-table.html:

    The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

      Someone mentioned something about a trigger table I think. That monitors that table and updates its value when changed. Any idea on how to do this?

      Edit:
      For example, a trigger that updates the uuid column from the table users whenever a new record is inserted and sets the value to the function UUID().

        I think this might work... I found this post at mysql forums.

        When you want to use

        SET NEW.col_name = value

        in your trigger, please note that you CANNOT use this with the AFTER the action, and must use it BEFORE the action.

        Therefore, this will work:

        CREATE TRIGGER sdata_insert BEFORE INSERT ON sometable
        FOR EACH ROW
        BEGIN
        SET NEW.guid = UUID();
        END
        ;

        And this will NOT work:

        CREATE TRIGGER sdata_insert AFTER INSERT ON sometable
        FOR EACH ROW
        BEGIN
        SET NEW.guid = UUID();
        END
        ;

          I run this query

           CREATE TRIGGER uuid_users_insert BEFORE INSERT ON `users`
FOR EACH
ROW
BEGIN
SET NEW.uuid = UUID( )

          But I got this error

          #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

            Pulsorock wrote:

            Someone mentioned something about a trigger table I think. That monitors that table and updates its value when changed. Any idea on how to do this?

            Edit:
            For example, a trigger that updates the uuid column from the table users whenever a new record is inserted and sets the value to the function UUID().

            I suppose you could do that, but it would seem a lot easier to simply add the UUID() function to your insert query.

              NogDog wrote:

              I suppose you could do that, but it would seem a lot easier to simply add the UUID() function to your insert query.

              I could... but I have several scripts inserting records to that table and I prefer doing the change on one place instead of doing all the code changes. 🙂

                Sounds like a case for a function in an include file or, better yet, a class method that could be called by any of those scripts to do their inserts. 🙂

                  Write a Reply...