Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-882

Increasing size of varchar type using ALTER TABLE can implicitly change the column from NOT NULL to NULLable.

    XMLWordPrintableJSON

    Details

    • 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
    • Labels:
      None
    • Environment:
      generic

      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

        Attachments

          Activity

            People

            • Assignee:
              bandaram Satheesh E. Bandaram
              Reporter:
              bandaram Satheesh E. Bandaram
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: