CODEWITHSUNDEEP
db2
user2529774
user2529774

Reputation: 47

db2 change column from null to not null

Have a Location column in XYZ table in db2, now I want to change to not null and using the below command

ALTER table xyz ALTER COLUMN LOCATIONID set not null

But asking to give default value. How to change the command for that

Upvotes: 0

Views: 4795

Answers (1)

mao
mao

Reputation: 12267

As you are making a previously optional column into a mandatory column, if there is already at least one row in the table that contains a NULL in LOCATIONID then Db2 may prevent the alteration (SQL0407N).

If the table has no rows, or if no rows have null in LOCATIONID column, then Db2-LUW will allow the alteration. You may need to REORG the table before/after the alteration in some cases.

If the table already has rows with LOCATIONID null, you must either set these rows LOCATIONID value to some not-null value before doing the alteration, or you must recreate the table.

When recreating the table, consider specifying a default value via 'NOT NULL WITH DEFAULT ...' if that makes sense for the data concerned.

Upvotes: 1

Related Questions