Add default value to existing column

Question

Somehow this customer's database didn't update correctly, and now they have some number columns which should have a default value of 0 with no default value (but not null), and it creates an error that the column cannot be null when I try to do an insert.

I tried:

ALTER TABLE cesMisc ALTER COLUMN int1 int DEFAULT((0))

But it doesn't like the DEFAULT in the alter column. How can I define a default value to the column?

Answer 1

try:

create table xyz
(
column1 int not null
)

ALTER TABLE xyz ADD CONSTRAINT
    DF_column1 DEFAULT 0 FOR column1
GO