Change TEXT column default from null to '' (empty string)

Question

I have a table where default value is not specified at the time of table creation. Now I want to change default value to '' (empty string). When I run alter table query it get success but still on new row insertion table consider NULL as default value if column value is not specified.

TABLE Schema::

CREATE TABLE `table1` (
  `col1` INT(11) NOT NULL AUTO_INCREMENT,
  `col2` TEXT,
  `col3` INT(11) DEFAULT NULL,
  `col4` TINYINT(1) DEFAULT '0',
  PRIMARY KEY (`id`)
);

ALTER Query::

ALTER TABLE `table1` change `col2` `col2` text  default '';

You should not apply default to col2, because its TEXT type and it cannot be have DEFAULT. — JDGuide, Apr 22 '13 at 08:23
@realnumber3012 - Unless I misunderstand you, that is part of a query; it won't change the column's DEFAULT value, as OP requests, so would need to be incorporated into ALL QUERIES that add a new row to the table! Or you might be proposing it as part of a constraint that acts after any row change to the table, in which case a fully fleshed out answer is needed, to show how that would be done. — ToolmakerSteve, Aug 25 '15 at 01:46

Salman A · Answer 1 · 2015-08-26T05:23:28.180

21

Blob and text columns cannot have a DEFAULT value (Ref). Depending on platform, MySQL may generate a warning or error when you try to do that.

Change the datatype to something more appropriate (e.g. VARCHAR).

edited Aug 26 '15 at 05:23

answered Apr 22 '13 at 08:09

Salman A

262,204
82
430
521

You are right @Salman. Blob and text type cannot have default value. – JDGuide Apr 22 '13 at 08:20
3

@ToolmakerSteve - varchar's max length is no longer 255 with any of the newer versions of mysql. – Uncle Iroh Aug 25 '15 at 22:47

Sashi Kant · Answer 2 · 2013-04-22T07:43:42.730

2

Use modify

ALTER TABLE `table1` MODIFY column `col2` text default '';

edited Apr 22 '13 at 07:43

answered Apr 22 '13 at 07:37

Sashi Kant

13,277
9
44
71

score 2 · Answer 3 · edited May 23 '17 at 12:25

First of all the Table schema is incorrect. It will throw error: Key column 'id' doesn't exist in table

CREATE TABLE `test_table1` (
  `col1` INT(11) NOT NULL AUTO_INCREMENT,
  `col2` TEXT,
  `col3` INT(11) DEFAULT NULL,
  `col4` TINYINT(1) DEFAULT '0',
  PRIMARY KEY (`id`)
);

Correcting the question itself

CREATE TABLE `test_table1` (
  `col1` INT(11) NOT NULL AUTO_INCREMENT,
  `col2` TEXT,
  `col3` INT(11) DEFAULT NULL,
  `col4` TINYINT(1) DEFAULT '0',
  PRIMARY KEY (`col1`)
);

Now if you'll run the Alter statment

ALTER TABLE `table1` change `col2` `col2` text  default '';

The error message is: BLOB, TEXT, GEOMETRY or JSON column 'col2' can't have a default value. Reference

Good Read: Why can't a text column have a default value in MySQL?

score 0 · Answer 4 · answered Apr 22 '13 at 07:37

0

ALTER TABLE `table1` change `col2` `col2` text  NOT NULL default '';

answered Apr 22 '13 at 07:37

Zaffy

16,801
8
50
77

score 0 · Answer 5 · answered Apr 22 '13 at 08:16

0

Try this

ALTER TABLE yourtable_name ALTER COLUMN columnname SET DEFAULT ' ' ;

Its working for me.Hope it will help you.

answered Apr 22 '13 at 08:16

JDGuide

6,239
12
46
64

1

Won't let me edit my comment... I did this on varchar not text – Engineiro Apr 27 '13 at 16:51
6

This is string ' ' containing space. It's not empty. – Swatantra Kumar Jan 25 '17 at 13:20
you can .trim() it to make it an empty string. But yeah, that's not an empty string. – Jonathan Laliberte Jul 21 '17 at 22:11
JDGuide: is it deliberate that you set the default value to a non-empty string (one space), even though that is not what was asked (nor is commonly done, IMHO)? If that was accidental, please consider altering your answer. Until then, this is an incorrect answer to the question. Or needs explanation. – ToolmakerSteve Oct 28 '19 at 11:55

score 0 · Answer 6 · answered Sep 02 '15 at 05:46

0

In phpmyadmin go to "change" option for specific Field and select Default2 as As defined: and leave below text box empty

For example:

Field frequency
Type VARCHAR
Length/Values1 20

Default2 As defined: and Text box empty

Last click on Save button

answered Sep 02 '15 at 05:46

Gourav Agaste

21
2

score -1 · Answer 7 · edited Nov 08 '17 at 04:06

-1

Alter table structure using the following query USE MODIFY ALTER TABLE <table> MODIFY <column> VARCHAR(<XX>) DEFAULT '';

edited Nov 08 '17 at 04:06

jafarbtech

6,842
1
36
55

answered Nov 08 '17 at 00:35

billmask

1
2

Change TEXT column default from null to '' (empty string)

7 Answers7