Gibraltar Support

How can we help you today?

Start a new topic
Discussions VistaDB Questions
Answered

SQL - Add a column with a default value

Hi,


I can't add a column to a table and define a default value. 

ALTER TABLE MYTEST ADD NUMBEROFSTUDENTS int NOT NULL DEFAULT (0)

 

Fails with:

Open SQL Query : ALTER TABLE MYTEST ADD NUMBEROFSTUDENTS int NOT NULL DEFAULT (0)
*Length can not be less than zero
*Parameter Name: length
Error 509 (Provider v. 5.0.4.1289): Invalid or incomplete statement: TABLE
Line #: 1; Column #: 7

(Please note the lines started with * are translated by me - in my system they are shown in my local language).


As a result, if the table has already some rows I cannot also use:

 

ALTER TABLE MYTEST ADD NUMBEROFSTUDENTS INT NOT NULL

(since there is no default value to be set to the rows already in the table).


A workaround could be 3 separate statements:

ALTER TABLE MYTEST ADD NUMBEROFSTUDENTS SMALLINT INT
UPDATE MYTEST SET NUMBEROFSTUDENTS=0
ALTER TABLE MYTEST ALTER COLUMN SMALLINT INT NOT NULL

 

- but this doesn't solve the default issue for new columns.

I believe that DEFAULT is not supported by VistaDB - do you plan to support this syntax in the future?

    Best Answer

    VistaDB does support column defaults.  The oddity is that it's picky about the order of the declaration even though SQL Server isn't, and we'll put that on our backlog to resolve.


    The following syntax works:


     

    ALTER TABLE APPLICATION ADD NUMBEROFSTUDENTS int DEFAULT (0) NOT NULL

     


    3 CommentsSorted by Oldest First
    Answer

    VistaDB does support column defaults.  The oddity is that it's picky about the order of the declaration even though SQL Server isn't, and we'll put that on our backlog to resolve.


    The following syntax works:


     

    ALTER TABLE APPLICATION ADD NUMBEROFSTUDENTS int DEFAULT (0) NOT NULL

     


    Great!


    I can live with this "oddity" - probably the effort needed to implement this would be more usefull in other questions.

    However I didn't find this in the documentation - it would be nice to have these diferences documented in the help.


    Keep up the good work in VistaDB!



    This is another case where people tend to have their preferred style - I always wrote it like you did, others on the engine team wrote it in the other order and the Microsoft documentation doesn't seem to show either example but instead prefer a third syntax that lets you set the name of the constraint.  That said, I think it's important we support the syntax form you started with because I've seen it used by Visual Studio and other SQL Server tooling.

    Login to post a comment
    More topics in Questions
    See all 99 topics