How to alter a column and change the default value

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] How to alter a column and change the default value?

Examples related to mysql

Examples related to sql