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?

octopusgrabbus · Accepted Answer · 2012-05-16 00:33:09Z

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, 2012 at 0:33

octopusgrabbus

10.7k15 gold badges75 silver badges137 bronze badges

answered May 15, 2012 at 9:41

Tschareck

4,26912 gold badges49 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Klausos Klausos Over a year ago

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.

Tschareck Over a year ago

UPDATE TABLE MyTable SET Col3 = null WHERE Col3='0000-00-00'

Tschareck Over a year ago

Wait... isn't Col3 a varchar(20), not Date ?

Bud Damyanov Over a year ago

Empty strings and NULL values are different things, in MySQL query browser it is a little bit confusing this.

Hamid Talebi Over a year ago

I do it without "DEFAULT" keyword.is it necessary?

|

eggyal · Accepted Answer · 2012-05-15 09:39:32Z

30

ALTER TABLE MyTable MODIFY Col3 varchar(20) NULL;

answered May 15, 2012 at 9:39

eggyal

127k18 gold badges220 silver badges244 bronze badges

7 Comments

Klausos Klausos Over a year ago

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.

Sushivam Over a year ago

if i try ALTER TABLE spen_recipe MODIFY Null varchar(20) NULL; i get err

eggyal Over a year ago

@SachinS: What's that first Null doing there?

Sushivam Over a year ago

have jus set the description as default to unsigned NOT NULL

eggyal Over a year ago

@SachinS: It should be ALTER TABLE <table_name> MODIFY <column_name> <data_type> NULL;.

|

marc_s · Accepted Answer · 2022-05-24 07:56:22Z

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, 2022 at 7:56

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 23, 2022 at 10:31

EHP

11 bronze badge

Comments

SagitSri · Accepted Answer · 2022-08-04 09:42:03Z

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 4, 2022 at 9:42

answered Aug 3, 2022 at 8:13

SagitSri

5316 silver badges16 bronze badges

1 Comment

Trenton McKinney Over a year ago

This answer was reviewed in the Low Quality Queue. Here are some guidelines for How do I write a good 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

Collectives™ on Stack Overflow

Alter SQL table - allow NULL column value

4 Answers 4

7 Comments

7 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

7 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related