on 2015 Sep 03 10:38 AM
When I add a non-nullable column to a non-empty table, I certainly have to add a default.
IIRC, with older versions, I had to use a two-step approach because adding a non-nullable column on non-empty tables was not allowed at all):
ALTER TABLE MyTable ADD MyCol1 bit NULL DEFAULT 0;
ALTER TABLE MyTable MODIFY MyCol1 NOT NULL;
With newer versions (say, v12), I can do that as desired in one step:
ALTER TABLE MyTable ADD MyCol1 bit NOT NULL DEFAULT 0;
However, this fails with an -116 SQL CODE error ("Table must be empty") when trying to add more than one such column:
ALTER TABLE MyTable
ADD MyCol1 bit NOT NULL DEFAULT 0,
ADD MyCol2 bit NOT NULL DEFAULT 0;
(The same is true for adding table contraints, AFAIK.)
Why is that limitation - and is it documented anywhere?
Request clarification before answering.
The limitation was not documented, and has been partially relaxed for SQLA 16 with performance improvements to ALTER TABLE statement. The limitation itself was related to implementation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
33 | |
22 | |
17 | |
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.