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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

this worked for me   ALTER TABLE  Table   Alter COLUMN  Column  VARCHAR 50  not null

User · Answer

You can change the definition of existing DB column using following sql   ALTER TABLE mytable modify mycolumn datatype NOT NULL

User · Answer

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

User · Answer

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

[sql-server] Altering a column: null to not null

Examples related to sql-server

Examples related to tsql

Examples related to null

Examples related to alter-table

Examples related to alter-column