8

How do I change my column's default value from None to something else? For example, I want my dates to have a default value of 0000-00-00 if I don't specify one when I create the row.

I understand this in phpMyAdmin, but I'm not sure how to do it via command prompt.

I also understand how to do this when adding a column. But all of my columns are made and have data in some of them.

ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;

From searching, I found this line, but I'm not sure if that's what I want?

ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';
AstroCB
  • 12,101
  • 20
  • 56
  • 70
User
  • 22,193
  • 36
  • 109
  • 193

3 Answers3

13

Use ALTER TABLE to CHANGE or MODIFY the DEFAULT value of column. Check this link ALTER TABLE SYNTAX

ALTER TABLE `tableName` CHANGE `columnName` `columnName` DATE DEFAULT '0000-00-00'; 
ALTER TABLE `tableName` MODIFY `columnName` DATE DEFAULT '0000-00-00';
Saharsh Shah
  • 27,975
  • 8
  • 43
  • 82
  • Will this apply 0000-00-00 to all my old rows that currently have None, or do I need to run a query to change those? – User Jan 04 '14 at 05:03
  • 2
    @macdonjo It will apply `0000-00-00` to all rows where the field is `null` in your column. – Saharsh Shah Jan 04 '14 at 05:08
0

try this

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

user CHANGE to alter a existing column

see Link

Satish Sharma
  • 9,475
  • 6
  • 27
  • 51
0

In my case, it worked

ALTER TABLE `table_name` CHANGE `col_name` `col_name_again` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'anydefault_text';

Hope it helps you too.