CODEWITHSUNDEEP
sqlsql-server-2008
user710502
user710502

Reputation: 11471

Create a column and make it default to a value

Hello I have a column in my table that is of type bit, and i will be running a script for many many databases that checks if it exists if it does i need to alter is and add a column and default its bit value to 1 as a default

I tried the following but when it created the column it still shows NULL what am i missing?

ALTER TABLE [myTable] ADD [ShippingOption] [bit] NULL DEFAULT 1

Thank you

Upvotes: 2

Views: 157

Answers (3)

DeCaf
DeCaf

Reputation: 6086

When a default constraint is applied to a nullable column such as in this case, the default value is applied only on inserts when the column name is excluded from the list.

Normally this does not apply when you create the field. Edit: However, as pointed out by Martin, it is possible to get the desired effect by adding WITH VALUES.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453067

Just use WITH VALUES to have the new column take the default value rather than NULL

ALTER TABLE [myTable]
ADD [ShippingOption] [bit] NULL DEFAULT 1 WITH VALUES;

This is not required if your newly added column is marked as NOT NULL. In that case the default will be applied automatically.

Upvotes: 4

p.campbell
p.campbell

Reputation: 100567

It sounds like you've created the column with the default value. All future inserts will use this default value if unspecified.

You say "it still shows NULL". You'll need to update the existing records:

UPDATE myTable
SET ShippingOption = 1
WHERE ShippingOption IS NULL;

The reason your default didn't apply: your column is still nullable. So on creation, the default wasn't required to be applied.

You could alternatively specify that the column be NOT NULL in your ALTER statement. This would ensure that your default would be applied without requiring you to issue an UPDATE, as well as ensuring that any statement would require a non-NULL value when inserting:

ALTER TABLE [MyTable] ADD [ShippingOption] [bit] NOT NULL DEFAULT 1;

Upvotes: 0

Related Questions