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.

Solution 1

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

Solution 2

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];

Solution 3

You will have to do it in two steps:

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

UPDATE MyTable SET MyNullableColumn = 0
WHERE MyNullableColumn IS NULL

2. Alter the table to change the property of the column

ALTER TABLE MyTable
ALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL

Solution 4

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.

Solution 5

this worked for me:

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

View more solutions

Comments

[Marc Gravell]

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

[Martin Smith]

Also you may be surprised to know that under some circumstances altering a column to NOT NULL can cause a lot of logging.

[TechTravelThink]

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.

[mpen]

Backup your database first in case you make a typo and your DB explodes.

[Vivian River]

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.

[John Bowers]

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"

[siride]

@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.

[Igor Mironenko]

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.

[Greg Dougherty]

Did you read the part about "adding one more line"? As in, this is in addition to the above answer?

[Igor Mironenko]

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

[blindguy]

do you have to re-type the column (say i dont want to look them all up) or can i just omit that part?

[Ben Taliadoros]

For Oracle sql ALTER TABLE tablename MODIFY (columnname type NOT NULL); worked for me

[jocull]

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.

[jpmc26]

It appears that in Oracle 11, you don't have to repeat the datatype. Merely columname NOT NULL is enough. See the documentation.

[Elyas Hadizadeh]

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 ;-)