Altering a column: null to not null

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

• 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
• Backup your database first in case you make a typo and your DB explodes. - mpen
• Its strange the MS SQL Management Studio does not allow that option, it brags about "Dropping table ..." - Sebastian
• 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. - Daniel Allen Langdon
• @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

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

• 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
• Did you read the part about "adding one more line"? As in, this is in addition to the above answer? - Greg Dougherty
• 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
• 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

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

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.

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

this worked for me:

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

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

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.

• yes, but google directs people here.. It recieved lots of upvotes cause it, and lots of downwotes. I think its useful. Everyone is free to ignore this, but I honestly think and see this is helping people. - csomakk
• Please, read SO rules. How do I write a good answer? , ANSWERING TECHNICAL QUESTIONS HELPFULLY and Write the perfect question. Notes: Don’t be afraid to delete (or edit heavily) useless answers and Don’t “answer and run - Kiquenet

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.

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"

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

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

2. 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:

3. 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:

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;

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

ALTER TABLE mytable modify mycolumn datatype NOT NULL;