db2 change column from null to not null
up vote
0
down vote
favorite
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
db2
asked Nov 9 at 19:01
user2529774
412
add a comment |
up vote
0
down vote
favorite
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
db2
asked Nov 9 at 19:01
user2529774
412
what db2 version and platform are you using?
– Paul Vernon
Nov 9 at 22:22
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
db2
asked Nov 9 at 19:01
user2529774
412
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
db2
db2
asked Nov 9 at 19:01
user2529774
412
asked Nov 9 at 19:01
user2529774
412
asked Nov 9 at 19:01
user2529774
412
asked Nov 9 at 19:01
user2529774
412
asked Nov 9 at 19:01
user2529774
412
412
what db2 version and platform are you using?
– Paul Vernon
Nov 9 at 22:22
add a comment |
what db2 version and platform are you using?
– Paul Vernon
Nov 9 at 22:22
what db2 version and platform are you using?
– Paul Vernon
Nov 9 at 22:22
what db2 version and platform are you using?
– Paul Vernon
Nov 9 at 22:22
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
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.
answered Nov 9 at 19:28
mao
3,4951316
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
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.
answered Nov 9 at 19:28
mao
3,4951316
add a comment |
up vote
1
down vote
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.
answered Nov 9 at 19:28
mao
3,4951316
add a comment |
up vote
1
down vote
up vote
1
down vote
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.
answered Nov 9 at 19:28
mao
3,4951316
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.
answered Nov 9 at 19:28
mao
3,4951316
edited Nov 9 at 19:55
answered Nov 9 at 19:28
mao
3,4951316
answered Nov 9 at 19:28
mao
3,4951316
answered Nov 9 at 19:28
mao
3,4951316
3,4951316
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53231828%2fdb2-change-column-from-null-to-not-null%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
what db2 version and platform are you using?
– Paul Vernon
Nov 9 at 22:22