1

in this said

the default value must be a constant; it cannot be a function or an expression

can you tell me why ? why we must give constant default value?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Yuda Prawira
  • 12,075
  • 10
  • 46
  • 54
  • 1
    Because sometimes it'd be helpful it if wasn't; I'd sometimes quite like the default `time` to be `now()` for example. Knowing *why* sometimes makes an irritation easier to deal with... – David Thomas Jul 16 '11 at 22:18
  • Actually this is not specific to SQL. Almost all programming languages are created the way that only constants can be used as default values for variables. – Karolis Jul 16 '11 at 22:19

3 Answers3

4

This is a limitation in MySQL.
You can either use another RDBMS or get around the problem using a trigger.

CREATE TRIGGER yourtable_insert BEFORE INSERT ON `yourtable`
    FOR EACH ROW SET NEW.youraddedcolumn = NOW(), NEW.yourupdatedcolumn = NOW();
Community
  • 1
  • 1
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
1

One reason I can think of is ambiguity. Should the expression be evaluated before storing it as a default or for each INSERT?

Karoly Horvath
  • 94,607
  • 11
  • 117
  • 176
0

Note that there's one non-constant value you can use in a table definition:

stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP

This can only be used on a TIMESTAMP column, but it's useful enough.