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?

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

31

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,555
15
68
131

answered May 15 '12 at 09:41

Tschareck

4,071
9
47
74

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 29 · Answer 2 · answered May 15 '12 at 09:39

29

ALTER TABLE MyTable MODIFY Col3 varchar(20) NULL;

answered May 15 '12 at 09:39

eggyal

122,705
18
212
237

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 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

732,580
175
1,330
1,459

answered May 23 '22 at 10:31

EHP

1
1

SagitSri · Answer 4 · 2022-08-04T09:42:03.320

0

ALTER TABLE school MODIFY COLUMN school_van varchar(36) DEFAULT NULL;

"ALTER" keyword didn't work but "MODIFY" worked fine in MySQL 8.0.26

edited Aug 04 '22 at 09:42

answered Aug 03 '22 at 08:13

SagitSri

491
6
15

This answer was reviewed in the [Low Quality Queue](https://stackoverflow.com/help/review-low-quality). Here are some guidelines for [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer). Code only answers are **not considered good answers**, and are likely to be downvoted and/or deleted because they are **less useful** to a community of learners. It's only obvious to you. Explain what it does, and how it's different / **better** than existing answers. [From Review](https://stackoverflow.com/review/low-quality-posts/32398208) – Trenton McKinney Aug 03 '22 at 21:44

Alter SQL table - allow NULL column value

4 Answers4

Linked