Modify a column to NULL - Oracle

Question

I have a table named CUSTOMER, with few columns. One of them is Customer_ID.

Initially Customer_ID column WILL NOT accept NULL values.

I've made some changes from code level, so that Customer_ID column will accept NULL values by default.

Now my requirement is that, I need to again make this column to accept NULL values.

For this I've added executing the below query:

ALTER TABLE Customer MODIFY Customer_ID nvarchar2(20) NULL

I'm getting the following error:

ORA-01451 error, the column already allows null entries so
therefore cannot be modified

This is because already I've made the Customer_ID column to accept NULL values.

Is there a way to check if the column will accept NULL values before executing the above query...??

You can find the answer here: http://stackoverflow.com/questions/1028956/modify-column-in-oracle-how-to-check-if-a-column-is-nullable-before-setting-to — TheEwook, Mar 27 '13 at 11:38

score 14 · Accepted Answer · answered Mar 27 '13 at 11:43

You can use the column NULLABLE in USER_TAB_COLUMNS. This tells you whether the column allows nulls using a binary Y/N flag.

If you wanted to put this in a script you could do something like:

declare

   l_null user_tab_columns.nullable%type;

begin

   select nullable into l_null
     from user_tab_columns
    where table_name = 'CUSTOMER'
      and column_name = 'CUSTOMER_ID';

   if l_null = 'N' then
      execute immediate 'ALTER TABLE Customer 
                          MODIFY (Customer_ID nvarchar2(20) NULL)';
   end if;

end;

It's best not to use dynamic SQL in order to alter tables. Do it manually and be sure to double check everything first.

score 6 · Answer 2 · answered Oct 01 '14 at 22:23

6

Or you can just ignore the error:

declare
    already_null exception;
    pragma exception_init (already_null , -01451);
begin
    execute immediate 'alter table <TABLE> modify(<COLUMN> null)';
    exception when already_null then null;
end;
/

answered Oct 01 '14 at 22:23

grokster

5,919
1
36
22

score 2 · Answer 3 · answered Mar 25 '21 at 11:10

You might encounter this error when you have previously provided a DEFAULT ON NULL value for the NOT NULL column.

If this is the case, to make the column nullable, you must also reset its default value to NULL when you modify its nullability constraint.

eg:

DEFINE table_name = your_table_name_here
DEFINE column_name = your_column_name_here;

ALTER TABLE &table_name
  MODIFY (
    &column_name
      DEFAULT NULL
      NULL
  );

score 1 · Answer 4 · answered Mar 28 '13 at 03:50

I did something like this, it worked fine. Try to execute query, if any error occurs, catch SQLException.

try {
stmt.execute("ALTER TABLE Customer MODIFY Customer_ID nvarchar2(20) NULL");
} catch (SQLException sqe) {
Logger("Column to be modified to NULL is already NULL : " + sqe);
}

Is this correct way of doing?

Nishat Lakhani · Answer 5 · 2016-02-08T09:58:51.373

To modify the constraints of an existing table

for example... add not null constraint to a column.

Then follow the given steps:

1) Select the table in which you want to modify changes.

2) Click on Actions.. ---> select column ----> add.

3) Now give the column name, datatype, size, etc. and click ok.

4) You will see that the column is added to the table.

5) Now click on Edit button lying on the left side of Actions button.

6) Then you will get various table modifying options.

7) Select the column from the list.

8) Select the particular column in which you want to give not null.

9) Select Cannot be null from column properties.

10) That's it.

Modify a column to NULL - Oracle

5 Answers5