I've got a column in my table that's a bigint without a default, and there's a sequence generator in the database called dbVersion. I want to change the column so its default is dbVersion.NextVAL, but dbisql keeps giving me an error when I try to change it:

ALTER TABLE MyTable ALTER FromToVersion SET DEFAULT dbVersion.NextVAL

The error I get is:

Syntax error near "(end of line)" on line 1

Also, I tried to do this in Sybase Central and I get the same message. What am I doing wrong?

asked 10 May '13, 20:26

TonyV's gravatar image

TonyV
1.2k333967
accept rate: 75%


According to the documentation for CREATE TABLE and ALTER TABLE if you want to use a sequence expression you must enclose it in parenthesis - see 'default-value:' on these pages.

Example:

ALTER TABLE MyTable ALTER FromToVersion SET DEFAULT ( dbVersion.NextVAL )

HTH

permanent link

answered 10 May '13, 20:35

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269
accept rate: 40%

edited 10 May '13, 20:37

Thank you, that was it. I guess I needed to put the parentheses into the default value dialog in Sybase Central, too.

(10 May '13, 20:38) TonyV
1

Mark, according to the Help, SET is only allowed with SET COMPUTE, but apparently SET is a noise word for other attributes.

I.e., these both work, but SET DEFAULT should not (according to the Help):

ALTER TABLE MyTable ALTER FromToVersion SET DEFAULT ( dbVersion.NextVAL );

ALTER TABLE MyTable ALTER FromToVersion DEFAULT ( dbVersion.NextVAL );

Interestingly, this works (which is in agreement with the Help)...

ALTER TABLE MyTable ALTER x SET COMPUTE ( 2 + 2 );

and this throws a syntax error (as it should, according to the Help)...

ALTER TABLE MyTable ALTER x COMPUTE ( 2 + 2 );

...did y'all follow that?

In other words, you can't blame the Help if the parser is funky :)

alt text

PS I once studied the BNF generated from the YACC for SA9, and this is FAR from being the only funkiness :)

(11 May '13, 08:58) Breck Carter
Replies hidden
2

Breck: You are correct. I had a look at the grammar and SET COMPUTE and SET DEFAULT are explicitly declared in the grammar. The ALTER DEFAULT is allowed due to a different grammar production which allows modification of various column attributes - and this other production includes the DEFAULT value but not the COMPUTE clause. I'm not sure why the grammar is structured the way it is... likely historical reasons beyond the scope of understandability?

I'm not surprised that the documentation doesn't follow the grammar exactly. The CREATE TABLE and ALTER TABLE grammar goes on for several thousands lines (and this is just the grammar!) so to reproduce this in the documentation in any form would make users' head spin.

I will make a note for doc team to indicate the SET is an optional word when altering the column DEFAULT.

(11 May '13, 09:36) Mark Culp

Only SELECT is more complex than ALTER TABLE... or is it? :)

(12 May '13, 09:22) Breck Carter
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:

×90
×7

question asked: 10 May '13, 20:26

question was seen: 766 times

last updated: 12 May '13, 11:02