How to add a not null constraint in an existing column in DB2 iSeries?

Question

I have tried the following scripts to add a not null constraint to my column.

ALTER TABLE MYDB.RULES ALTER TYPEID SET NOT NULL;
ALTER TABLE MYDB.RULES ALTER COLUMN TYPEID SET NOT NULL;

I also get this error when I execute it:

 Reason code 10. [SQL State=57014, DB Errorcode=-952] 

Is it possible to do this in DB2 iSeries?

Answer 1

First you have to identify and handle the NULL values of the target table ( update, delete, etc. )

SELECT COUNT(1) FROM MYDB.RULES WHERE TYPEID IS NULL;

Than you can create the constraint on the target table.

ALTER TABLE MYDB.RULES ALTER COLUMN TYPEID SET NOT NULL;