Avatar of Nakuru1234
Nakuru1234
 asked on

ORA-01449: column contains NULL values; cannot alter to NOT NULL

Hello,

I am executing this query but I keep getting an error message

ALTER TABLE UTG.PARAMETER
 ADD CONSTRAINT PARAMETER_PK
 PRIMARY KEY
 (PARAM_ID, CIR_TYPE)

Error at line 1
ORA-01449: column contains NULL values; cannot alter to NOT NULL

How can I be able to add a PK on both columns (PARAM_ID, CIR_TYPE) and make them NOT NULL without this error message?

TIA,
N.
Oracle Database

Avatar of undefined
Last Comment
Gerwin Jansen

8/22/2022 - Mon
SOLUTION
Muhammad Ahmad Imran

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
David VanZandt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Nakuru1234

ASKER
Please provide the sql syntax to add the values or to update the rows in the PARAMETER table, replacing NULL with a not null value. Thanks.

N.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gerwin Jansen

DESCribe the table please so we can try creating an update statement, if possible show some records, redacting contents if needed.
Nakuru1234

ASKER
//DESCribe the table please so we can try creating an update statement
Table name is PARAMETER
Column names; PARAM_ID (VARCHAR2) (32) and CIR_TYPE (VARCHAR2) (16)

//if possible show some records, redacting contents if needed.
PARAM_ID      CIR_TYPE
frameFmt      ds1
lineCode                      ds1
tid                      ds1
aid                      ds1
ortn                      ds1
direction                      ds1
hexBinPattern      ds1
pattern                      ds1

I just tried this again...

SQL> ALTER TABLE UTG.PARAMETER
 ADD PRIMARY KEY
 (PARAM_ID, CIR_TYPE)
ALTER TABLE UTG.PARAMETER
 ADD PRIMARY KEY
 (PARAM_ID, CIR_TYPE)
Error at line 3
ORA-01449: column contains NULL values; cannot alter to NOT NULL

N.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nakuru1234

ASKER
select count * from utg.parameter where param_id is NULL;

  COUNT(*)
----------
         0
1 row selected.


select count * from utg.parameter where cir_type is NULL;

  COUNT(*)
----------
         6
1 row selected.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nakuru1234

ASKER
Yes, that worked :) Thank you.

N.
David VanZandt

Thanks gerwin for following up with my earlier example :) https://www.experts-exchange.com/Database/Oracle/Q_27732481.html#a38012666
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gerwin Jansen

@dvz - Uhm, yes... I was asking for some details and yes I filled in the blanks....