How do you change the default for a column?

[Former Member]

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?

[MarkCulp · Accepted solution]

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

[Former Member · Accepted solution]

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

[Breck_Carter · Accepted solution]

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 🙂

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

[MarkCulp · Accepted solution]

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.

[Breck_Carter · Accepted solution]

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