Altering a column: null to not null

Question

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved.

I am looking for the specific SQL syntax to alter a column (call it ColumnA) to "not null". Assume the data has been updated to not contain nulls.

Using SQL server 2000.

One other thing - you might want to add a default to that any existing inserts that don't specify the column don't fail: ALTER TABLE FOO ADD CONSTRAINT FOO_Bar_Default DEFAULT 0 FOR Bar — Marc Gravell, Mar 27 '09 at 13:36
Also you may be surprised to know that [under some circumstances](http://dba.stackexchange.com/a/29600/3690) altering a column to `NOT NULL` can cause a lot of logging. — Martin Smith, Aug 31 '13 at 11:38

score 2194 · Accepted Answer · edited Apr 19 '20 at 11:23

2194

First, make all current NULL values disappear:

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

Then, update the table definition to disallow "NULLs":

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

edited Apr 19 '20 at 11:23

Arsen Khachaturyan

7,904
4
42
42

answered Mar 27 '09 at 13:30

mdb

52,000
11
64
62

252

Backup your database first in case you make a typo and your DB explodes. – mpen Sep 01 '10 at 07:32
58

Let me value-add to the above two correct responses. The semantics of NULL can have a few meanings. One of the common meaning is UNKNOWN. Take SCORE as an example. A null SCORE and a ZERO SCORE is a world of difference! Before you did the above recommendations. Make sure you application does not take null in its business logic. – TechTravelThink Mar 27 '09 at 13:31
16

Databases should always be backed up. You never know when one of your people might write and UPDATE or DELETE statement and forget the WHERE clause. – Vivian River Oct 07 '13 at 22:03
6

I know the question was for SQLServer, but this didn't quite work for Postgres 9. Instead using try: "ALTER TABLE [Table] ALTER COLUMN [COLUMN] SET NOT NULL" – John Bowers Oct 28 '14 at 17:22
2

@SebastianGodelet: there's a setting that allows you to turn off that warning, or make it so that it doesn't prevent you from modifying the table. In some cases, changing the schema of a table requires that a new table be created, the data copied over from the old and the old table dropped. Because an error in this process could result in data loss, SSMS warns you and, by default, prevents you from doing it. – siride Mar 15 '15 at 16:15
2

do you have to re-type the column (say i dont want to look them all up) or can i just omit that part? – blindguy Feb 26 '16 at 19:27
For Oracle sql ALTER TABLE tablename MODIFY (columnname type NOT NULL); worked for me – Ben Taliadoros Jul 04 '16 at 13:50
I would also specify a column default value of (0). This would prevent existing code that doesn't specify the col value to not break and produce the intended inserted value. – Shiv Feb 03 '23 at 01:02

Greg Dougherty · Answer 2 · 2016-06-16T21:44:14.313

66

I had the same problem, but the field used to default to null, and now I want to default it to 0. That required adding one more line after mdb's solution:

ALTER TABLE [Table] ADD CONSTRAINT [Constraint] DEFAULT 0 FOR [Column];

edited Jun 16 '16 at 21:44

answered Jun 15 '12 at 20:45

Greg Dougherty

3,281
8
35
58

This doesn't answer the problem. The default is already set, it's the NOT NULL which needs to be changed. With only your answer, all existing records will stay NULL and the problem remains. – PandaWood Aug 26 '15 at 00:16
7

Did you read the part about "adding one more line"? As in, this is in addition to the above answer? – Greg Dougherty Aug 28 '15 at 04:17
8

Actually, no it's not clear that "adding one more line" means "in addition to the above answer" (especially since there are many answers "above") - if that's what you meant, then the wording really needs to change and you should include the line of the answer you refer to – PandaWood Aug 28 '15 at 04:31
4

Just wanted to chime in and say that it's a good idea to explicitly name your constraints, defaults included. If you ever need to drop a column you'll have to know the name of the constraint to drop before you'll be able to. Ran into this in our DB migrations a few times. I know the example includes it here, but it's still a place some devs trip up because the name is not required. – jocull Feb 21 '17 at 20:42

score 41 · Answer 3 · edited Apr 10 '18 at 15:23

41

You will have to do it in two steps:

Update the table so that there are no nulls in the column.

UPDATE MyTable SET MyNullableColumn = 0
WHERE MyNullableColumn IS NULL

Alter the table to change the property of the column

ALTER TABLE MyTable
ALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL

edited Apr 10 '18 at 15:23

answered Mar 27 '09 at 13:34

Ralph Wiggum

699
1
6
10

`SET MyNullableColumn = 0` only works if your existing column is a numeric column. For non-numeric types you need to set another default value like and empty string or default date, etc. But this answer is fully relevant to the above-mentioned question and correct ;-) – Elyas Hadizadeh Jan 06 '21 at 07:04

score 30 · Answer 4 · edited Sep 17 '12 at 21:18

30

For Oracle 11g, I was able to change the column attribute as follows:

ALTER TABLE tablename MODIFY columnname datatype NOT NULL;

Otherwise abatichev's answer seemed good. You can't repeat the alter - it complains (at least in SQL Developer) that the column is already not null.

edited Sep 17 '12 at 21:18

j0k

22,600
28
79
90

answered Sep 17 '12 at 21:13

JDM

349
3
2

It appears that in Oracle 11, you don't have to repeat the `datatype`. Merely `columname NOT NULL` is enough. See [the documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm#SQLRF53129). – jpmc26 Jun 29 '18 at 18:15

score 20 · Answer 5 · answered Jun 05 '12 at 13:00

20

this worked for me:

ALTER TABLE [Table] 
Alter COLUMN [Column] VARCHAR(50) not null;

answered Jun 05 '12 at 13:00

Dheeraj Sam

369
2
5

score 15 · Answer 6 · answered Mar 27 '09 at 13:26

15

As long as the column is not a unique identifier

UPDATE table set columnName = 0 where columnName is null

Then

Alter the table and set the field to non null and specify a default value of 0

answered Mar 27 '09 at 13:26

Eppz

3,178
2
19
26

score 5 · Answer 7 · edited Jun 06 '15 at 12:24

In case of FOREIGN KEY CONSTRAINT... there will be a problem if '0' is not present in the column of Primary key table. The solution for that is...

STEP1:

Disable all the constraints using this code :

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

STEP2:

RUN UPDATE COMMAND (as mentioned in above comments)
RUN ALTER COMMAND (as mentioned in above comments)

STEP3:

Enable all the constraints using this code :

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Philip Kirkbride · Answer 8 · 2017-01-26T20:20:09.450

4

In my case I had difficulties with the posted answers. I ended up using the following:

ALTER TABLE table_name CHANGE COLUMN column_name column_name VARCHAR(200) NOT NULL DEFAULT '';

Change VARCHAR(200) to your datatype, and optionally change the default value.

If you don't have a default value you're going to have a problem making this change, as default would be null creating a conflict.

edited Jan 26 '17 at 20:20

answered Jan 26 '17 at 19:39

Philip Kirkbride

21,381
38
125
225

score 4 · Answer 9 · edited May 31 '13 at 09:43

4

this seems simpler, but only works on Oracle:

ALTER TABLE [Table] 
ALTER [Column] NUMBER DEFAULT 0 NOT NULL;

in addition, with this, you can also add columns, not just alter it. It updates to the default value (0) in this example, if the value was null.

edited May 31 '13 at 09:43

P Varga

19,174
12
70
108

answered Mar 04 '12 at 07:55

csomakk

5,369
1
29
34

Tony L. · Answer 10 · 2016-09-07T15:29:03.293

Making column not null and adding default can also be done in the SSMS GUI.

As others have already stated, you can't set "not null" until all the existing data is "not null" like so:

UPDATE myTable SET myColumn = 0

Once that's done, with the table in design view (right click on table and click "design view"), you can just uncheck the Allow Nulls columns like so:

Still in design view with the column selected, you can see the Column Properties in the window below and set the default to 0 in there as well like so:

score 1 · Answer 11 · edited Apr 26 '19 at 12:03

1

Let's take an example:

TABLE NAME=EMPLOYEE

And I want to change the column EMPLOYEE_NAME to NOT NULL. This query can be used for the task:

ALTER TABLE EMPLOYEE MODIFY EMPLOYEE.EMPLOYEE_NAME datatype NOT NULL;

edited Apr 26 '19 at 12:03

Zoe

27,060
21
118
148

answered Apr 26 '19 at 11:58

Ashish Dwivedi

45
6

how to do this for multiple columns? for example, making employee_name and employee_email not null simultaneously? What is the syntax? Below syntax gives error ***alter table employee alter column employee_name varchar(20) not null, alter column employee_email varchar(20) not null;*** – that_noob Jul 27 '22 at 18:28

score 0 · Answer 12 · answered Jun 13 '14 at 00:45

0

For the inbuilt javaDB included in the JDK (Oracle's supported distribution of the Apache Derby) the below worked for me

alter table [table name] alter column [column name] not null;

answered Jun 13 '14 at 00:45

trooper31

182
1
2
9

score 0 · Answer 13 · answered Nov 12 '18 at 09:03

0

You can change the definition of existing DB column using following sql.

ALTER TABLE mytable modify mycolumn datatype NOT NULL;

answered Nov 12 '18 at 09:03

Rashmi singh

151
1
5

Golden Lion · Answer 14 · 2021-04-30T20:56:29.847

First make sure the column that your changing to not does not have null values select count(*) from table where column's_name is null
Impute the missing values. you can replace the nulls with empty string or 0 or an average or median value or an interpolated value. It depends on your back fill strategy or forward fill strategy.
Decide if the column values need to be unique or non-unique. if they need to be unique than add an unique constraint. Otherwise, see if performance is adequate or if you need to add an index.

Altering a column: null to not null

14 Answers14

Linked

Related