Change a Nullable column to NOT NULL with Default Value

Question

I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()).

So far I've got the following script, which works fine provided that I've cleaned up all the nulls beforehand:

ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL

Is there any way to also specify the default value as well on the ALTER statement?

do you want to fill all the already existing records with today's date? — froadie, Jul 06 '10 at 13:52

score 110 · Accepted Answer · edited Aug 08 '23 at 01:19

110

I think you will need to do this as three separate statements. I've been looking around and everything I've seen seems to suggest you can do it if you are adding a column, but not if you are altering one.

ALTER TABLE dbo.MyTable
ADD CONSTRAINT my_Con DEFAULT GETDATE() for created

UPDATE MyTable SET Created = GetDate() where Created IS NULL

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL

edited Aug 08 '23 at 01:19

Pang

9,564
146
81
122

answered Jul 06 '10 at 14:10

Abe Miessler

82,532
99
305
486

15

Has anyone else actually *tried* this? I've found that the second statement fails due to null value in the table (despite the presence of a default value). You need to insert a step between those two where you clean out the existing nulls. – Jeremy Stein Nov 01 '12 at 15:21
5

Looks like someone edited the answer. It is now correct and complete. – Krisztián Balla Apr 07 '15 at 07:07

score 13 · Answer 2 · edited Nov 01 '12 at 15:18

13

You may have to first update all the records that are null to the default value then use the alter table statement.

Update dbo.TableName
Set
Created="01/01/2000"
where Created is NULL

edited Nov 01 '12 at 15:18

Jeremy Stein

19,171
16
68
83

answered Jul 06 '10 at 14:24

Gage

7,365
9
47
77

score 10 · Answer 3 · answered Oct 07 '15 at 16:34

you need to execute two queries:

One - to add the default value to the column required

ALTER TABLE 'Table_Name` ADD DEFAULT 'value' FOR 'Column_Name'

i want add default value to Column IsDeleted as below:

Example: ALTER TABLE [dbo].[Employees] ADD Default 0 for IsDeleted

Two - to alter the column value nullable to not null

ALTER TABLE 'table_name' ALTER COLUMN 'column_name' 'data_type' NOT NULL

i want to make the column IsDeleted as not null

ALTER TABLE [dbo].[Employees] Alter Column IsDeleted BIT NOT NULL

Does not appear to work with SQL Server: the null values *already in* the table are not affected. — user2864740, Oct 16 '18 at 20:06

score 3 · Answer 4 · edited Aug 08 '23 at 01:20

3

If it's SQL Server, you can do it on the column properties within design view.

Try this:

ALTER TABLE dbo.TableName 
  ADD CONSTRAINT DF_TableName_ColumnName
    DEFAULT '01/01/2000' FOR ColumnName

edited Aug 08 '23 at 01:20

Pang

9,564
146
81
122

answered Jul 06 '10 at 13:53

BenW

1,312
10
18

This needs to be in script form so that it can be run as part of our automated release process. – Jim B Jul 06 '10 at 13:55
Still no dice. The constraint gets added; but it still barfs when it tries to convert the NULL to NOT NULL – Jim B Jul 06 '10 at 14:16
Script to update all the nulls to set the value to your default then apply it? Sorry, ain't being much help! – BenW Jul 06 '10 at 14:19
I eliminated the statement to convert null to not null and the solution works. Since there is a default value defined, all my new entries are getting my set default so I appear to have no risk for null values. Just make sure your code never sets a null value in your update statements. – ShadeTreeDeveloper Nov 17 '14 at 12:47

score 1 · Answer 5 · edited Aug 08 '23 at 01:20

1

Try this

ALTER TABLE table_name ALTER COLUMN col_name data_type NOT NULL;

edited Aug 08 '23 at 01:20

Pang

9,564
146
81
122

answered Nov 10 '20 at 10:49

belal ahmad

59
6

Change a Nullable column to NOT NULL with Default Value

5 Answers5

Linked