CODEWITHSUNDEEP
sqlsql-server
Umang Gupta
Umang Gupta

Reputation: 13

Incorrect syntax near default in alter table

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

Upvotes: 1

Views: 1642

Answers (2)

Thom A
Thom A

Reputation: 96016

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.

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272396

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

ALTER TABLE dbo.ImagingEvents ADD DEFAULT 1 FOR Site_SiteId

Upvotes: 3

Related Questions