Alter SQL table - allow NULL column value

Question

Initially, the table "MyTable" has been defined in the following way:

CREATE TABLE IF NOT EXISTS `MyTable` (
  `Col1` smallint(6) NOT NULL AUTO_INCREMENT,
  `Col2` smallint(6) DEFAULT NULL,
  `Col3` varchar(20) NOT NULL,
);

How to update it in such a way that the column "Col 3" would be allowed to be NULL?

Has this question been answered? If so don't forget to mark an answer as accepted. — RyanfaeScotland, Aug 25 '14 at 10:58

score 28 · Answer 1 · answered May 15 '12 at 09:39

28

ALTER TABLE MyTable MODIFY Col3 varchar(20) NULL;

answered May 15 '12 at 09:39

eggyal

118,441
18
203
234

When I edit the column (try to make it empty) in MySQL Query Browser, the following error message occurs: Data truncated for column 'Col 3' at row 1. It happens after your updates. – Klausos Klausos May 15 '12 at 09:42
if i try ALTER TABLE spen_recipe MODIFY Null varchar(20) NULL; i get err – Sushivam Nov 04 '16 at 12:23
@SachinS: What's that first `Null` doing there? – eggyal Nov 04 '16 at 12:26
have jus set the description as default to unsigned NOT NULL – Sushivam Nov 04 '16 at 12:30
@SachinS: It should be `ALTER TABLE MODIFY NULL;`. – eggyal Nov 04 '16 at 12:33
@David: did you intend that to be a comment on my answer? Not sure I understand what you mean. – eggyal May 19 '21 at 16:00
@eggyal: MODIFY COLUMN works on MySQL and Oracle prior to 10G version. – David May 19 '21 at 19:14

score 28 · Accepted Answer · edited May 16 '12 at 00:33

28

The following MySQL statement should modify your column to accept NULLs.

ALTER TABLE `MyTable`
ALTER COLUMN `Col3` varchar(20) DEFAULT NULL

edited May 16 '12 at 00:33

octopusgrabbus

10,250
13
64
126

answered May 15 '12 at 09:41

Tschareck

3,751
8
44
73

Yeah,but when I edit the column Col 3 (Date), it get the value '0000-00-00' automatically. But I want it to be equal to NULL. – Klausos Klausos May 15 '12 at 09:46
3

UPDATE TABLE MyTable SET Col3 = null WHERE Col3='0000-00-00' – Tschareck May 15 '12 at 09:48
3

Wait... isn't Col3 a varchar(20), not Date ? – Tschareck May 15 '12 at 09:51
Empty strings and NULL values are different things, in MySQL query browser it is a little bit confusing this. – Bud Damyanov May 15 '12 at 09:56
I do it without "DEFAULT" keyword.is it necessary? – Hamid Talebi Feb 01 '16 at 10:20
I don't think the provided code actually works, according to the [spec](http://dev.mysql.com/doc/refman/5.7/en/alter-table.html): `ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}` it should be ``ALTER TABLE `MyTable` ALTER COLUMN `Col3` SET DEFAULT NULL``, shouldn't it? – rifflock Sep 08 '16 at 23:34
I have a column name 'Null' which has values 'No'...how to alter it as 'Yes' – Sushivam Nov 04 '16 at 12:22

score 0 · Answer 3 · edited May 24 '22 at 07:56

0

This works in PSQL, not sure if it also works in normal SQL.

ALTER TABLE tablename
ALTER COLUMN columnname DROP NOT NULL;

edited May 24 '22 at 07:56

marc_s

704,970
168
1,303
1,425

answered May 23 '22 at 10:31

EHP

1
1

Alter SQL table - allow NULL column value

3 Answers3

Linked