Performing Calculation in Default MySQL Value

database, mysql

Solution

No. The value for the DEFAULT clause must be a constant. (The one exception to this rule is the use of `CURRENT_TIMESTAMP` as a default value for a `TIMESTAMP` column.)

As an alternative, you can use a TRIGGER to set a value for a column when a row is inserted or updated.

For example, within a BEFORE INSERT FOR EACH ROW trigger, you can perform calculations from values supplied for other columns and/or query data from other tables.

EDIT

For the example given in the EDIT of the question, an example starting point for a trigger definition:

CREATE TRIGGER mytable_bi 
BEFORE INSERT ON mytable 
FOR EACH ROW
BEGIN
  SET NEW.percentage = (100.0 * NEW.marks) / NULLIF(NEW.total_marks,0);
END

Problem

Is there a way to performing calculations based on value of some other column within same or different table using the Default Value property (that is, the `DEFAULT` clause on a column definition) of MySQL ? We can set static default values to any column but can we perform calculations or query other tables' data ?? EDIT Let's say a table with column for `marks` and other with `total_marks` and third column `percentage`. How to set default value of `percentage` to be calculated from the former two columns

Original source