[DERBY-882] Increasing size of varchar type using ALTER TABLE can implicitly change the column from NOT NULL to NULLable.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.1.3.1, 10.2.1.6
• Fix Version/s: 10.1.3.1, 10.2.1.6
• Component/s: SQL
• Environment: generic
• Assignee: Satheesh E. Bandaram
• Reporter: Satheesh E. Bandaram
• Created: 27/Jan/06 16:20
• Updated: 31/Jan/06 05:46
• Resolved: 31/Jan/06 05:46

Description

Altering size of a varchar column using ALTER TABLE command can change the column from not nullable to nullable.

ij version 10.1
ij> connect 'jdbc:derby:bdb;create=true';
ij> create table a (id integer not null, name varchar(20) not null, primary key(name));
0 rows inserted/updated/deleted
ij> insert into a values (1, 'abc');
1 row inserted/updated/deleted
ij> insert into a values (2, null);
ERROR 23502: Column 'NAME' cannot accept a NULL value. <==== Initially doesn't accept nulls
ij> alter table a alter name set data type varchar(50); <==== Change size of varchar column
0 rows inserted/updated/deleted
ij> insert into a values (3, 'hijk');
1 row inserted/updated/deleted
ij> insert into a values (4, null); <==== Now NULLs are accepted
1 row inserted/updated/deleted
ij> select * from a;
ID |NAME
--------------------------------------------------------------
1 |abc
3 |hijk
4 |NULL