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

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.