2

I want to add a new Column to an existing Table that has already Data in it. The Column should be NOT NULL. Because of that i want to set a Default Value. But when i do it throws the following exception: "Incorrect syntax near 'for'"

ALTER TABLE Semester ADD SIDNew uniqueidentifier NOT NULL 
CONSTRAINT DF_SIDNew DEFAULT '00000000-0000-0000-0000-000000000000' FOR SIDNew

I already had a look at How to set a default value for an existing column and Incorrect syntax near the keyword 'FOR' but none of them helped me.

Community
  • 1
  • 1
P.Haldemann
  • 46
  • 8

2 Answers2

5

Simply lose the part FOR SIDNew. You are adding a new column with a default constraint. You are not adding a new default constraint to an existing column.

Andrew Carmichael
  • 3,086
  • 1
  • 22
  • 21
Mithrandir
  • 24,869
  • 6
  • 50
  • 66
3

The FOR keyword is only required if you're adding default values to Azure SQL Data Warehouse or Parallel Data Warehouse. If you're using normal relational SQL Server (on-premise or Azure) then you can just write it without the FOR

ALTER TABLE Semester ADD SIDNew uniqueidentifier NOT NULL 
CONSTRAINT DF_SIDNew DEFAULT '00000000-0000-0000-0000-000000000000';

https://msdn.microsoft.com/en-gb/library/ms190273.aspx

Andrew Carmichael
  • 3,086
  • 1
  • 22
  • 21