SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alter Column - Add Not Null


Alter Column - Add Not Null

Author
Message
Deepar_07
Deepar_07
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 16

Hi,

I need to make a existing Nullable column in a table to a Not Null and also add a default constraint to the column to store value "0" (zero).

I've tried the following command but it comes with error.

ALTER TABLE DATA
ALTER COLUMN [IsTested]
SET DEFAULT 0 WITH VALUES
GO

ALTER TABLE DATA
ALTER COLUMN [IsTested] [bit] NOT NULL
GO

Please let me know if you have any solution

Deepa




Deepa
ramses2nd
ramses2nd
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 294

To Add a default constraint of 0 and then change the filed to NOT NULL I would use the following:


ALTER TABLE DATA ADD CONSTRAINT
DF_DATA_istested DEFAULT 0 FOR istested
GO

ALTER TABLE DATA
ALTER COLUMN [IsTested] [bit] NULL
GO





Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2657 Visits: 699

To create a new default and also seed with the WITH VALUES clause only works when adding a new column, not when changing an existing column.

What you need to do is three things.

Add the default
Update the column to the default value where it is null.
Alter the column to not null

The first two steps can be done in any order.

ALTER TABLE DATA
add constraint DF_IsTested DEFAULT(0) FOR IsTested
GO

UPDATE DATA
SET IsTested = 0
WHERE IsTested IS NULL
GO

ALTER TABLE DATA
ALTER COLUMN [IsTested] [bit] NOT NULL
GO

/Kenneth





Deepar_07
Deepar_07
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 16

Thanks Kenneth.

I hope there is no other way to set the default and not null together in one command.

Deepa




Deepa
Kenneth Wilhelmsson
Kenneth Wilhelmsson
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2657 Visits: 699

As far as I've been able to find out, when changing an existing column - No. When adding a new column - Yes.

Though, what difference does it make anyway?
Whenever we want to perform something that is made up of more than one distinct operation, but we want to handle all the different steps as one single command, we have the opportunity to do those within a transaction.

Thus, even though you
1) add a default
2) update all null to the default value
3) change column null to not null

..by enclosing all three within a BEGIN TRANSACTION and COMMIT TRANSACTION block, they all become one logical command anyway.

..or is there some other compelling reason for you wanting the 'single command' so bad?

/Kenneth





N Cook
N Cook
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 22
ALTER TABLE [dbo].[TableName] ADD [ColumnName] bit NOT NULL
CONSTRAINT Default_ColumnName DEFAULT 0
GO
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search