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,337
  • 20
  • 57
  • 73
User
  • 23,729
  • 38
  • 124
  • 207

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
  • 28,687
  • 8
  • 48
  • 83
  • 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,547
  • 6
  • 29
  • 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.