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

On February 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.