Re: NOT NULL WITH DEFAULT ... Create vs. Alter

[Charles Wilt <charles.wilt@xxxxxxxxx>]

David,

In your example, F1 is explicitly defined NOT NULL and implicitly defined
without a default.

Thus for example, this insert will fail.
insert into TEST1 (F4) values ('A')

with the error: Null values not allowed in column or variable F1.

As defined, F1 is a required column.  It must always be specified in an
INSERT.  There's no valid default for it.

When adding a column to an existing table, it's assumed there are existing
rows.  Thus, you must specify a default to load into the new column on the
existing rows.

Charles

Charles

On Wed, Feb 3, 2016 at 1:23 PM, David Gibbs <david@xxxxxxxxxxxx> wrote:

> Folks:
>
> I've observed something interesting recently.
>
> If you create a table with a field that's specified as 'NOT NULL', the
> table will be created fine.
>
> CREATE TABLE TEST1 (F1 CHAR (10) NOT NULL)
>
> If you try to ADD a column to that table with a field that's also
> specified 'NOT NULL', the column WILL NOT be added because it lacks a
> default value.
>
> ALTER TABLE TEST1 ADD COLUMN F4 CHAR (10) NOT NULL
> NOT NULL clause not allowed.
>
> If you specify a implicit or explicit default value for the column, the
> column will be created fine.
>
> ALTER TABLE TEST1 ADD COLUMN F4 CHAR (10) NOT NULL WITH DEFAULT
> ALTER completed for table TEST1.
>
> Any idea why the table can be created without a default value on the
> column but can't be altered in the same way?
>
> As a side note, at least on 6.1, if you specify a CCSID value on the add
> column statement, the lack of a default value does not generate an error.
>
> ALTER TABLE TEST1 ADD COLUMN F4 CHAR (10) NOT NULL CCSID 37
> ALTER completed for table TEST1
>
> david
>
>
>
> --
> IBM i on Power Systems: For when you can't afford to be out of business!
>
> I'm riding 100 miles (a full century) in the American Diabetes
> Association's Tour de Cure to raise money for diabetes research, education,
> advocacy, and awareness.  You can make a tax deductible donation to my ride
> by visiting http://emailsig.diabetessucks.net.  My goal is $6000 but any
> amount is appreciated.
>
> See where I get my donations from ... visit
> http://emailsig.diabetessucks.net/map for an interactive map (it's a
> geeky thing).
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.