You are here: Home / Programming / How to change a NULL column to NOT NULL and vice versa in SQL [Quick Tip]

How to change a NULL column to NOT NULL and vice versa in SQL [Quick Tip]

By 2 Comments

Share this article

Problem:

An already existing table (sat table_name) has a column (say column_name) with data type (data_type) defined as NOT NULL. You want to change this column to a Null field.

Solution:

Try either this command:

ALTER TABLE table_name MODIFY column_name data_type NULL;

e.g. ALTER TABLE MyTable MODIFY FirstName VARCHAR2(60) NULL;

or this command:

ALTER TABLE table_name ALTER COLUMN column_name data_type NULL;

eg. ALTER TABLE MyTable ALTER COLUMN FirstName VARCHAR2(60) NULL;

To change from a Null column to Not Null, use the following command:

ALTER TABLE table_name MODIFY column_name data_type NOT NULL;

or

ALTER TABLE table_name ALTER COLUMN column_name data_type NOT NULL;

Hope that helps you. Enjoy :) Don’t hesitate to ask questions.

  • Martin Belanger

    the change to Not Null will work as long as you don’t have any NULL in any of the rows. If you do, you need to update them before. It might seem obvious, but I had some clients that left some column Nullable because of that.

    • https://computerandyou.net/ Abhishek Prakash

      Hi Martin,

      I was in a bit of hurry when I wrote the post…and forgot to mention the obvious fact… Thanks for the tip.. I’ll soon update the post…