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.

Answer 1

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

Answer 2

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.

Answer 3

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:

Answer 4

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

ALTER TABLE mytable modify mycolumn datatype NOT NULL;

Answer 5

this worked for me:

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

Answer 6

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.