1

I have a table col called unique_id which is set to NULL by default.

How can I "create a patch" and alter the table so when the unique_id is NULL to generate a random code (contains sting like this EE7B35CB25D24E34D7B5DE2A) 

ALTER TABLE `projects`
CHANGE `unique_id` char('24') = RAND()
COLLATE 'latin1_swedish_ci' NULL 
AFTER `status`;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ltdev
  • 4,037
  • 20
  • 69
  • 129
  • You can set auto increment to your unique id – dgk Nov 28 '16 at 12:22
  • the `unique_id` is not like `id` which is primary key, in addition it could have letters and number - I'm going to update the question as I did a mistake – ltdev Nov 28 '16 at 12:24
  • Trigger is a good way then which @Rahul mention as below. – dgk Nov 28 '16 at 12:26

1 Answers1

2

Only way I see (since you need a unique random number) is using BEFORE INSERT trigger. Check for NULL and update the column with random number using RAND() function probably.

Using RAND() with DEFAULT is not possible since DEFAULT expects the argument to be static / constant and not any expression (Thought of making the pointer, if this comes to your mind as a possible solution).

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • did an update, its not nessecarry numbers, it can be a string like this EE7B35CB25D24E34D7B5DE2A – ltdev Nov 28 '16 at 12:26
  • @Lykos, in that case also, you can follow the same approach except that you can't use `RAND()` function but your own UDF probably which will generate the custom random string. – Rahul Nov 28 '16 at 12:27
  • Not sure if I understand correct your answer, but what if I remove the default option?? – ltdev Nov 28 '16 at 12:29
  • @Lykos, your `default` option is useless in this scenario. Remove it. you will have to get it done through a trigger as already said. – Rahul Nov 28 '16 at 12:30