Re: Add Not null column to existing table

[Peter Brawley]

drop table t;
create table t(i int,j int);
insert into t values(1,null);
select * from t;
+------+------+
| i    | j    |
+------+------+
|    1 | NULL |
+------+------+
alter table t modify column j int not null;
ERROR 1138 (22004): Invalid use of NULL value
alter table t add column k int not null;
select * from t;
+------+------+---+
| i    | j    | k |
+------+------+---+
|    1 | NULL | 0 |
+------+------+---+

MySQL will not let you set a nullable column not null if null values for it exist. It will let you add a not null column and set missing values to the assumed default 0 or whatever default you specify, which is the sounder approach.