CODEWITHSUNDEEP
mysqlalter-table
Will
Will

Reputation: 895

How do I drop 'NOT NULL' from a column in MySQL?

A show create table command shows the following:

'columnA' varchar(6) NOT NULL DEFAULT '';

How do I modify that column so that the not null is removed? I need it to be:

'columnA' varchar(6) DEFAULT NULL;

I thought the following would work, but it has no effect:

ALTER TABLE tbl_name MODIFY columnA varchar(6) DEFAULT NULL;

Upvotes: 24

Views: 33220

Answers (3)

Bob Fanger
Bob Fanger

Reputation: 29917

Make the change (locally) in phpMyAdmin. It will show the query it used for the change. Execute this query in production and you're done.

You can use this strategy in any GUI tool to see the queries it performs. I personally use Sequel Pro (for Mac OS X) instead of phpMyAdmin.

Upvotes: 2

Rob Van Dam
Rob Van Dam

Reputation: 7980

Normally, Eric's answer should work:

ALTER TABLE tbl_name MODIFY columnA varchar(6) NULL DEFAULT NULL;

(Although the 'NULL DEFAULT NULL' part is optional).

But like you, I had a case that just returned OK without doing anything. In my case it appears to be due to the fact that my key was part of the primary key. So I had to do the following:

ALTER TABLE tbl_name DROP PRIMARY KEY;
ALTER TABLE tbl_name MODIFY columnA varchar(6);
ALTER TABLE tbl_name ADD PRIMARY KEY (columnA);

with that last query specifying whatever your primary key actually is.

Also, in case anyone thinks that is too verbose, the following combined query does NOT work, even though it should be identical:

ALTER TABLE tbl_name DROP PRIMARY KEY, MODIFY columnA varchar(6), ADD PRIMARY KEY (columnA);

I assume that mysql rewrites that last query into a different order so that the primary key still exists when the modify is performed, hence the need to break it out into three statements.

FYI, this is on mysql 5.1.47 but I haven't yet found any documentation indicating why this happens so I don't know what versions are affected.

Upvotes: 5

Eric Petroelje
Eric Petroelje

Reputation: 60559

Try this instead:

ALTER TABLE tbl_name MODIFY columnA varchar(6) NULL DEFAULT NULL;

Upvotes: 31

Related Questions