Incorrect syntax near default in alter table-CodePudding

I want to alter the existing column Site_SiteId in SQL Server to make it as not null with default value 1 but getting a syntax error:

ALTER TABLE dbo.ImagingEvents 
    ALTER COLUMN Site_SiteId bit NOT NULL DEFAULT 1

CodePudding user response：

default is a constraint so you need to add it to the table:

ALTER TABLE dbo.ImagingEvents ADD DEFAULT 1 FOR Site_SiteId

CodePudding user response：

First you need to ALTER the column:

ALTER TABLE dbo.ImagingEvents ALTER COLUMN Site_SiteId bit NOT NULL;

Note that if you have any rows that already have the value NULL you will need to UPDATE them first, before performing the ALTER.

Then, personally, I would recommend creating a named constraint, like so:

ALTER TABLE dbo.ImagingEvents ADD CONSTRAINT DF_Site_SiteId DEFAULT 1 FOR Site_SiteId;

Having named constraints, rather than the automatically named ones, is far better for transferable code.