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

Nakuru1234
Nakuru1234 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Muhammad Ahmad ImranDatabase Developer
Commented:
put some values for nulls then do your alter statement
David VanZandtOracle Database Administrator III
Commented:
The PK, by definition, may not contain NULL values.  One option may be to make the two columns indexed without the PK special constraint.  Otherwise, you will need to first update the rows in the PARAMETER table, replacing NULL with a not null value.

Author

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

David VanZandtOracle Database Administrator III
Commented:
That answer depends upon 1: the datatype of the columns involved and 2: what string or numeric value are acceptable to your customer.  We just can't say, "make alphanumeric columns equal to "ZZZZZZZ".

Secondly, for a PK:  no duplicate values permitted.  Common workarounds to plug in dummy data would be to use the SYSDATE for varchar2, or a sequence function for numeric.

UPDATE schemaname.tablename
SET columnname = {string | number|
WHERE columename = NULL;
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

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

Author

Commented:
//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.
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016
Commented:
Forgot to ask how many records contain NULLs, can you do:
select count * from utg.parameter where param_id is NULL;
select count * from utg.parameter where cir_type is NULL;

You may not have to update that many rows to be able to create the PK

Author

Commented:
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.
Topic Advisor
Most Valuable Expert 2016
Commented:
ok, try this:

update utg.parameter
set cir_type = 'ds9'
where cir_type is NULL;
commit;

This will set all NULLs to ds9, now try to create your PK

When this succeeds, you can lookup the ds9 records and update them as needed.

Author

Commented:
Yes, that worked :) Thank you.

N.
David VanZandtOracle Database Administrator III

Commented:
Thanks gerwin for following up with my earlier example :) http://www.experts-exchange.com/Database/Oracle/Q_27732481.html#a38012666
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial