How to add multiple columns to a table and add default constraint on one of them?

Question

I want to add 2 new columns to existing table.

One of them should be NOT NULL with default value 0 (filled in the existing rows as well).

I have tried the following syntax:

Alter TABLE dbo.MamConfiguration
    add [IsLimitedByNumOfUsers] [bit]  NOT NULL,
    CONSTRAINT IsLimitedByNumOfUsers_Defualt [IsLimitedByNumOfUsers] DEFAULT 0
    [NumOfUsersLimit] [int] NULL
go

But it throws exception. How should I write it?

KlaymenDK · Accepted Answer · 2017-01-23 17:30:23Z

56

You can use this:

ALTER TABLE dbo.MamConfiguration
ADD [IsLimitedByNumOfUsers] [BIT] NOT NULL DEFAULT 0,   
    [NumOfUsersLimit] [INT] NULL
GO

or this:

ALTER TABLE dbo.MamConfiguration
ADD [IsLimitedByNumOfUsers] [BIT] NOT NULL 
        CONSTRAINT IsLimitedByNumOfUsers_Default DEFAULT 0,
    [NumOfUsersLimit] [INT] NULL
go

More: ALTER TABLE

edited Jan 23, 2017 at 17:30

KlaymenDK

7329 silver badges33 bronze badges

answered Mar 3, 2013 at 10:59

Iswanto San

18.6k13 gold badges60 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

I would always recommend option #2 - give your constraints explicit names - makes it a lot easier if you need to drop them later on!

Iswanto San Over a year ago

@marc_s: yeah, I strongly agree with you!

Mike Over a year ago

I never noticed that you could use a , between columns to be added. What a time saver if you are adding a lot of new columns to an existing table!

marc_s · Accepted Answer · 2013-03-03 11:57:22Z

6

Try this.

ALTER TABLE dbo.MamConfiguration  
ADD [IsLimitedByNumOfUsers] [bit]  NOT NULL DEFAULT 0,     
[NumOfUsersLimit] [int] NULL

edited Mar 3, 2013 at 11:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 3, 2013 at 11:05

Praveen Nambiar

4,9022 gold badges24 silver badges32 bronze badges

Comments

Arulmouzhi · Accepted Answer · 2020-01-18 11:25:09Z

0

To add multiple columns to a table and add default constraint on one of them-

ALTER TABLE dbo.MamConfiguration
ADD [IsLimitedByNumOfUsers] [BIT] CONSTRAINT Def_IsLimitedByNumOfUsers DEFAULT(0) NOT NULL,   
    [NumOfUsersLimit] [INT] NULL;
GO

answered Jan 18, 2020 at 11:25

Arulmouzhi

2,31420 silver badges29 bronze badges

Comments

VolkanCetinkaya · Accepted Answer · 2022-09-22 11:08:49Z

0

Each column is different;

ALTER TABLE
    MamConfiguration
ADD
    Configuration1 BIT NULL, --this column is nullable
    Configuration2 BIT NOT NULL, --this column is not nullable
    Configuration3 BIT NOT NULL DEFAULT(0), --this column is not nullable and set default value is 0(zero)
    Configuration4 BIT NOT NULL CONSTRAINT DF_Configuration4 DEFAULT(0) --this column is not nullable and set default value is 0(zero) with spesific constraint name
GO

answered Sep 22, 2022 at 11:08

VolkanCetinkaya

6998 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to add multiple columns to a table and add default constraint on one of them?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related