Removing NOT NULL restriction from column in MySQL

Question

How can I alter a column whose restricted to NOT NULL to accept NULL values?

Possible duplicate of [How do I modify a MySQL column to allow NULL?](http://stackoverflow.com/questions/212939/how-do-i-modify-a-mysql-column-to-allow-null) — cellepo, Jul 18 '16 at 17:11

score 27 · Accepted Answer · answered Apr 18 '11 at 18:58

27

just modify it and put in the old type and leave off the not null

alter table table_name modify column foo int;

answered Apr 18 '11 at 18:58

regality

6,496
6
29
26

Will there be a Problem if the column is not empty? – Aufwind Apr 18 '11 at 18:59
not in mysql. in other dbms, such as oracle, it will not allow you alter a nonempty column, but mysql automagically casts things around for you when you alter columns. – regality Apr 18 '11 at 19:02
Thank you very much for the fast response(s). – Aufwind Apr 18 '11 at 19:09

score 1 · Answer 2 · answered Apr 18 '11 at 18:58

1

Assuming the table is table_name, column is column_name and its defined as varchar(200):

alter table table_name modify column column_name varchar(200) default null;

answered Apr 18 '11 at 18:58

lobster1234

7,679
26
30

score 1 · Answer 3 · answered Apr 18 '11 at 18:58

1

Try this:

ALTER TABLE mytable MODIFY mycolumn varchar(255) null;

answered Apr 18 '11 at 18:58

ANisus

74,460
29
162
158

score 1 · Answer 4 · answered Apr 18 '11 at 19:00

1

You can do this way:

ALTER TABLE tableName MODIFY columnName varchar2(100)

Replace tableName with your table name and columnName with your column name and also varchar2(100) to whatever data type you're using for this column

answered Apr 18 '11 at 19:00

Naveed Ahmad

3,176
1
15
18

Removing NOT NULL restriction from column in MySQL

4 Answers4