The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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?

asked 03 Sep '15, 09:38

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 04 Sep '15, 19:15

What version of SQL Anywhere are you running?

(03 Sep '15, 11:51) Elmi Eflov
Replies hidden

That has been tested with 12.0.1.4301.

(03 Sep '15, 11:56) Volker Barth

In version 16.0.0.2158 you can add multiple columns like that

(04 Sep '15, 07:59) Christian Ha...

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.

permanent link

answered 04 Sep '15, 16:03

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

converted 04 Sep '15, 16:08

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
6.5k2895

Yes, v16.0.0.2158 does allow several such additions in one single statement, so thanks for the clarification.

(Whereas altering two table check constraints in one statement still does lead to an "ALTER TABLE conflict" in v16, too - well, I can live with that:)

(04 Sep '15, 19:12) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×405
×11

question asked: 03 Sep '15, 09:38

question was seen: 260 times

last updated: 04 Sep '15, 19:15