MySQL default value using another column

Question

I have a table

-------------
| id | date |
-------------

Now i need to add column "week", so is it possible to add column like

alter table `a` add column `week` INT(6) not null default DATE_FORMAT(`date`, '%Y%v')

?

Why not try it? Then if you have an error or issue then ask the question. — dading84
– dading84, Commented Mar 8, 2018 at 14:50
You must use Trigger for this kind of functionality dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html — A.Baudouin
– A.Baudouin, Commented Mar 8, 2018 at 14:52

Paul Campbell · Accepted Answer · 2018-03-08 15:31:26Z

2

The comment above about using a trigger, is the way to go on this but doesn't elaborate on why you can't do it. So, from the documentation ...

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 TIMESTAMP and DATETIME columns.

answered Mar 8, 2018 at 15:31

Paul Campbell

1,9862 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL default value using another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related