i have a table like below:
create table info (username varchar(30),otherinfo varchar(100));
now i want to alter this table to have new field and this field has to have default value as
something like below:
alter table info add NewField varchar(100) default md5(username);
how to do so?
Thanks for your help
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Per MySQL docs (emphasis added) you cannot have an expression in a default value:
10.1.4. Data Type Default Values
The DEFAULT value clause in a data
type specification indicates 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.
I’ve tested that the following trigger works for your intent:
CREATE TRIGGER MyTriggerName BEFORE INSERT ON info FOR EACH ROW SET NEW.NewField = md5(NEW.username);
You should write a Trigger to achieve this functionality, given that MySQL has a md5 function built in or you are capable to either write or find a md5 algorithm for MySQL 🙂
Take a look @ http://dev.mysql.com/doc/refman/5.0/en/triggers.html
for the md5 function take a look here
Also, you should know that MD5 is not a secure algorithm anymore.