CODEWITHSUNDEEP
mysqlsql
qazwsx
qazwsx

Reputation: 26938

How to alter a column and change the default value?

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

Upvotes: 274

Views: 477256

Answers (8)

Vasu Gupta
Vasu Gupta

Reputation: 3

Use this approach:-

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

Upvotes: 0

Akash Dole
Akash Dole

Reputation: 511

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;

Upvotes: 50

Pradeep Kumar
Pradeep Kumar

Reputation: 4141

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 '{}';

Upvotes: 2

Sohail Ahmad
Sohail Ahmad

Reputation: 10227

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 '';

Upvotes: 0

Leonard Lepadatu
Leonard Lepadatu

Reputation: 646

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;

Upvotes: 5

Milan
Milan

Reputation: 3335

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;

Upvotes: 2

DaveJenni
DaveJenni

Reputation: 2021

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?

Upvotes: 176

fancyPants
fancyPants

Reputation: 51938

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 '{}';

Upvotes: 365

Related Questions