mysql - can I set VARCHAR default value as NULL?

Question

In mysql, i tried changing an existing table like this:

  ALTER TABLE  `etexts` CHANGE  `etext`  `etext` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT NULL

I got the response:

  #1067 - Invalid default value for 'etext'

Why?

score 11 · Accepted Answer · answered Aug 30 '10 at 13:53

11

It's contradictive... NOT NULL, but make it default NULL...
Remove DEFAULT NULL and change NOT NULL to NULL:

ALTER TABLE  `etexts` CHANGE  `etext`  `etext` VARCHAR( 100 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;

answered Aug 30 '10 at 13:53

Lekensteyn

Thanks - strangely enough, thats what phpmyadmin generated for me!! (what i had pasted above in my question) – tzmatt7447 Sep 04 '10 at 01:07
phpMyAdmin is not always right. You might want to fill in a bug if it's not already reported. – Lekensteyn Sep 04 '10 at 08:22

score 1 · Answer 2 · answered Aug 30 '10 at 13:56

1

You can't have a NOT NULL column defaulting to NULL.

If you want it to be NULLable then

... COLLATE latin1_swedish_ci NULL

NULLABLE columns will default to NULL automagically if no value is provided for the column

answered Aug 30 '10 at 13:56

StuartLC

2 Answers2