invalid ALTER TABLE option after changing a value to not null-CodePudding

create table agenda
(
    id         number(3),
    first_name varchar(20),
    last_name  varchar2(20),
    phone_nr   char(14)
);

alter table agenda 
    alter column phone_nr char(14) not null;

After I tried to change the phone_nr variable to not null, I get an error

Invalid ALTER TABLE option

I'm a beginner at SQL and I don't really understand where the problem is.

CodePudding user response：

AFAIK, you can't use an alter column clause to change a column's nullability, but you could use a modify column clause instead:

alter table agenda modify column phone_nr char(14) not null;
-- Here -----------^

CodePudding user response：

Did you make sure that all the phone_nr values are set to something that isn't null? You can only alter it to be not null if all values are something that is not null, otherwise it would contradict itself because its value would be null even though the table says it cannot be null. You can try this:

UPDATE agenda SET phone_nr = '0-000-000-0000' WHERE phone IS NULL;

Now, since all phone_nr values that were null are set to a value that is not null you can alter it to be NOT NULL in the table.