260

I got the following error while trying to alter a column's data type and setting a new default value:

ALTER TABLE foobar_data ALTER COLUMN col VARCHAR(255) NOT NULL SET DEFAULT '{}';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(255) NOT NULL SET DEFAULT '{}'' at line 1

Ben
  • 51,770
  • 36
  • 127
  • 149
qazwsx
  • 25,536
  • 30
  • 72
  • 106

8 Answers8

350
ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';

A second possibility which does the same (thanks to juergen_d):

ALTER TABLE foobar_data CHANGE COLUMN col col VARCHAR(255) NOT NULL DEFAULT '{}';
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • 14
    The `column` is optional. You could just use `ALTER TABLE foobar_data MODIFY col VARCHAR(255) NOT NULL DEFAULT '{}';` or `ALTER TABLE foobar_data CHANGE col col VARCHAR(255) NOT NULL DEFAULT '{}';` and the result will be same. – kapad Nov 22 '13 at 07:57
  • what does '{}' do? – Valor_ Sep 08 '18 at 14:24
  • 1
    It's the default string the OP was asking for. When you specify no value for this column when inserting a row, the value becomes `{}`. – fancyPants Sep 08 '18 at 19:41
  • 2
    Is writing two `col`s next to each other correct? (like this `col col`) – Shafizadeh Sep 30 '18 at 15:28
  • 4
    @Shafizadeh Yes, it is. This provides the possibility to rename a column. First one is original name, second is the new name. – fancyPants Sep 30 '18 at 19:15
  • 5
    Don't use this if you need to change only the `DEFAULT` value. This will process all the rows of the table for nothing (very long on huge tables). Use instead `ALTER TABLE ALTER COLUMN SET DEFAULT ` which is instant. – dolmen Jun 06 '19 at 20:02
  • 1
    Well, @dolmen, the question asked for how to "**alter a column's data type** and setting a new default value". So it's not really a reason to downvote, isn't it? – fancyPants Jun 06 '19 at 22:06
165

As a follow up, if you just want to set a default, pretty sure you can use the ALTER .. SET syntax. Just don't put all the other stuff in there. If you're gonna put the rest of the column definition in, use the MODIFY or CHANGE syntax as per the accepted answer.

Anyway, the ALTER syntax for setting a column default, (since that's what I was looking for when I came here):

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'literal';

For which 'literal' could also be a number (e.g. ...SET DEFAULT 0). I haven't tried it with ...SET DEFAULT CURRENT_TIMESTAMP but why not eh?

DaveJenni
  • 1,901
  • 1
  • 12
  • 4
  • 5
    Also current_timestamp wasn't working for me if quoted. I had to use the following: `ALTER TABLE table_name MODIFY COLUMN column_name TIMESTAMP NOT NULL DEFAULT current_timestamp;` – Nereis Feb 15 '16 at 06:53
  • 8
    +1 Best answer for me, because of by that way I don't need to specify the column's type and other things that will not change! – user2342558 Aug 09 '17 at 09:01
  • 3
    This is the efficient answer to change only the `DEFAULT` value. – dolmen Jun 06 '19 at 20:03
  • 1
    If you want the default value to be the time of the insert, use `NOW()` or `current_timestamp()` @Malaise @Nereis – BlueCacti Jul 01 '19 at 09:47
  • `ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT NOW();` makes syntax error in mysql 8.0.25! Anyone knows why? – Jerry Jan 27 '22 at 12:47
  • Works for me, accepted answer don't work for me. – Or Assayag Nov 08 '22 at 11:17
43

If you want to add a default value for the already created column, this works for me:

ALTER TABLE Persons
ALTER credit SET DEFAULT 0.0;
Full Stack Alien
  • 11,244
  • 1
  • 24
  • 37
Akash Dole
  • 441
  • 4
  • 4
  • 6
    This is the best answer as you do not need to copy the existing field specification. – rjh Sep 25 '19 at 09:59
4

For DEFAULT CURRENT_TIMESTAMP:

ALTER TABLE tablename
 CHANGE COLUMN columnname1 columname1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CHANGE COLUMN columnname2 columname2 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Please note double columnname declaration

Removing DEFAULT CURRENT_TIMESTAMP:

ALTER TABLE tablename
 ALTER COLUMN columnname1 DROP DEFAULT,
 ALTER COLUMN columnname2 DROPT DEFAULT;
Leonard Lepadatu
  • 606
  • 8
  • 14
2

In case the above does not work for you (i.e.: you are working with new SQL or Azure) try the following:

1) drop existing column constraint (if any):

ALTER TABLE [table_name] DROP CONSTRAINT DF_my_constraint

2) create a new one:

ALTER TABLE [table_name] ADD CONSTRAINT DF_my_constraint  DEFAULT getdate() FOR column_name;
Milan
  • 3,209
  • 1
  • 35
  • 46
2

Accepted Answer works good.

In case of Invalid use of NULL value error, on NULL values, update all null values to default value in that column and then try to do the alter.

UPDATE foobar_data SET col = '{}' WHERE col IS NULL;

ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';
Pradeep Kumar
  • 4,065
  • 2
  • 33
  • 40
0

Try this

ALTER TABLE `table_name` CHANGE `column_name` `column_name` data_type  NULL DEFAULT '';

like this

ALTER TABLE `drivers_meta` CHANGE `driving_license` `driving_license` VARCHAR(30) NULL DEFAULT '';
Sohail Ahmad
  • 7,309
  • 5
  • 27
  • 46
0

Use this approach:-

ALTER TABLE foobar_data CHANGE COLUMN col VARCHAR(255) NOT NULL SET DEFAULT '{}';

Vasu Gupta
  • 3
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 29 '22 at 23:53