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.

SOLUTION

Muhammad Ahmad Imran

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

David VanZandt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

David VanZandt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

SOLUTION

Gerwin Jansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Gerwin Jansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nakuru1234

ASKER

Yes, that worked :) Thank you.

N.

David VanZandt

Thanks gerwin for following up with my earlier example :) https://www.experts-exchange.com/questions/27732481/ORA-01449-column-contains-NULL-values-cannot-alter-to-NOT-NULL.html?anchorAnswerId=38012666#a38012666

Gerwin Jansen

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