SQL Server-fix: Error MSG 128 the name is not permitted in this context. Only constants, expressio

Reference: http://blog.sqlauthority.com/2007/03/22/sql-server-fix-error-msg-128-the-name-is-not-permitted-in-this-context-only-constants-expressions-or-variables-allowed-here-column-names-are-not-permitted/

 

 

Error message:

Server: MSG 128, Level 15, state 1, line 3
The name is not permitted in this context. Only constants, expressions, or variables allowed here. column names are not permitted.

Causes:

this error occurs when using a column as the default value of another column when a table is created.
Create Table [DBO]. [items] (
[ordercount] int ,
[productamount] int ,< br> [totalamount] default ( [ordercount] + [productamount] )
)
executing this create table statement will generate the following error message:

Server: MSG 128, Level 15, state 1, line 5
The name 'totalamount 'is not permitted in this context.
Only constants, expressions, or variables allowed here.
Column names are not permitted.

Solution/workaround/fix:

When setting the default value of a column make sure to use only constants, expressions or variables and not another column.
System functions can be used as default values. e.g. getdate (), newid ()
Use computed column in place of default keyword.
Create Table [DBO]. [items] (
[ordercount] int ,
[productamount] int ,< br> [totalamount] as ( [ordercount] + [productamount] )
)