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
K
Kendall Miller
said
almost 9 years ago
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
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
C
Carlos Crespo
said
almost 9 years ago
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!
Kendall Miller
said
almost 9 years ago
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.
Carlos Crespo
Hi,
I can't add a column to a table and define a default value.
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:
(since there is no default value to be set to the rows already in the table).
A workaround could be 3 separate statements:
- 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?
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:
- Oldest First
- Popular
- Newest First
Sorted by Oldest FirstKendall Miller
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:
Carlos Crespo
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!
Kendall Miller
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.
-
Corrupt database file
-
Copy Record From One Table to Another
-
VDB 4.2 - delete from [Table] where [Column] in(Value List)
-
Cannot see topics I have added
-
Tool for Compare 2 databases
-
How to create a computed field ?
-
c#.net Input Byte[] into image
-
VDB 4.2 DDA SetScope...
-
Slow attach to database file + error
-
Refreshing data from a bound grid
See all 99 topics