Altering a column: null to not null
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.
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
Read more... Read less...
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];
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
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.
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
Alter the table and set the field to non null and specify a default value of 0