alter column with default value

Question

alter table enquiry alter  column RejectReson int default 0

I want alter the column data type with default value zero,but it is not happening.

"it is not happening." - well, what *is* happening? Do you get an error message (if so, what?). Or different behaviour than you expected (if so, what?) — Damien_The_Unbeliever, Sep 27 '17 at 10:17
Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'default'. — Sandeep Singh, Sep 27 '17 at 10:19
@Tanner Not a valid dupe there, OP already has the column and wants to alter it, not add a new column. — DavidG, Sep 27 '17 at 10:28
@Tanner Probably should have left it, dupe targets are easily edited after the fact :) — DavidG, Sep 27 '17 at 10:31

score 1 · Answer 1 · edited May 22 '20 at 14:36

1

Use ADD CONSTRAINT.

For example:

ALTER TABLE enquiry ADD CONSTRAINT some_name DEFAULT 0 FOR RejectReson;

edited May 22 '20 at 14:36

GileBrt

answered Sep 27 '17 at 10:19

Amrut Patil

score 0 · Answer 2 · answered Sep 27 '17 at 10:19

0

ALTER TABLE enquiry ADD DEFAULT 0 FOR RejectReson

answered Sep 27 '17 at 10:19

score 0 · Answer 3 · answered Sep 27 '17 at 10:23

If you want to affect existing rows, then use with values:

alter table enquiry
    add constraint dft_enquiry_rejectreason default 0 with values;

This is explained in the documentation:

. . . WITH VALUES can be used to store the default value in the new column for each existing row in the table.

3 Answers3