sql server drop default syntax error

Question

Hi,

I am trying to drop the default value on a bit column, I have set it wrong and I want to do it again correctly. However, when I write:

ALTER TABLE Person
ALTER COLUMN tsHomePref DROP DEFAULT;

I get a 'incorrect syntax near keyword default error' and I don't know why

I want to drop the column and then build it again

ALTER TABLE Person
ADD COLUMN tsHomePref bit NOT NULL DEFAULT 0;

So, why won't it let me 'drop' the default value?

Thanks R.

Answer 1

You would need to do

ALTER TABLE Person 
    DROP CONSTRAINT DF__Person__tsHomePr__05BA7BDB

It helps if you use a consistent naming convention for these so you don't have to look in the system tables to get the name first.