Set default value for column of an existing table

Question

Set default value for column of an existing table

Navigation

#1 by (1 votes)

1

Is it possible to set a column's default value for an existing table?

I'm trying the following way, but the column always gets null instead of the default value that was entered.

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS C INNER JOIN SYSOBJECTS T ON C.id = T.id WHERE C.name = ('IsPreparacao') AND T.name = 'Cadastro')

BEGIN
ALTER TABLE Cadastro   
    ADD IsPreparacao bit  default 0
END

I have already searched the net and found nothing to help me as my problem, can anyone help me with this question?

Thank you very much for being able to help me.

sql-server

asked by anonymous 03.07.2015 / 15:03

1 answer

MVC PHP - Libraries - Directory Structure What to do for the javascript function n give reload on page

score 1 · Accepted Answer

Try this out

BEGIN
ALTER TABLE Cadastro   
        ADD IsPreparacao bit NOT NULL default 0
END

The NOT NULL condition will cause the existing records to be populated with the default value. A constraint ( NOT NULL ) will be created by default.

If you want to explicitly define the constraint name, or that your column may have NULL then you can use the following syntax.

ALTER TABLE Cadastro    
ADD IsPreparacao BIT NULL
CONSTRAINT IsPreparacaoDefault DEFAULT 0
WITH VALUES