CODEWITHSUNDEEP
sql-servert-sqlnullalter-tablealter-column
Karmic Coder
Karmic Coder

Reputation: 17949

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.

Upvotes: 1394

Views: 1624064

Answers (14)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4233

  1. First make sure the column that your changing to not does not have null values select count(*) from table where column's_name is null

  2. Impute the missing values. you can replace the nulls with empty string or 0 or an average or median value or an interpolated value. It depends on your back fill strategy or forward fill strategy.

  3. Decide if the column values need to be unique or non-unique. if they need to be unique than add an unique constraint. Otherwise, see if performance is adequate or if you need to add an index.

Upvotes: 0

mdb
mdb

Reputation: 52819

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

Upvotes: 2242

Ashish Dwivedi
Ashish Dwivedi

Reputation: 51

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;

Upvotes: 1

Rashmi singh
Rashmi singh

Reputation: 155

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

ALTER TABLE mytable modify mycolumn datatype NOT NULL;

Upvotes: 1

Ralph Wiggum
Ralph Wiggum

Reputation: 699

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
  1. Alter the table to change the property of the column
ALTER TABLE MyTable
ALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL

Upvotes: 41

Philip Kirkbride
Philip Kirkbride

Reputation: 22859

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.

Upvotes: 4

Tony L.
Tony L.

Reputation: 19396

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

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

enter image description here

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

enter image description here

Upvotes: 1

Greg Dougherty
Greg Dougherty

Reputation: 3461

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

Upvotes: 68

sam05
sam05

Reputation: 199

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"

Upvotes: 5

trooper31
trooper31

Reputation: 202

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;

Upvotes: 0

csomakk
csomakk

Reputation: 5497

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.

Upvotes: 5

JDM
JDM

Reputation: 359

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.

Upvotes: 31

Dheeraj Sam
Dheeraj Sam

Reputation: 389

this worked for me:

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

Upvotes: 22

Eppz
Eppz

Reputation: 3206

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

Upvotes: 15

Related Questions