forum

Home / DeveloperSection / Forums / Add non-nullable columns to an existing table in sql server?

Ask Question

Add non-nullable columns to an existing table in sql server?

Manoj Bhatt255106-May-2013
Hi Everyone!

I already have a table which consists of data. I need to alter table to add two new columns which are not null. How can I do that without losing any existing data?

Here's what I tried (via right-clicking the table and selecting Design):

Added new columns 'EmpFlag' (bit, null), 'CreatedDate' (datetime, null)

Updated 'EmpFlag' column in the table, to have some valid values. (Just wanted to work on one field, so I didn't update 'CreatedDate' field)

Now right clicked table, design, and made it not null.

When I tried to save, this error message appeared:

Thanks in advance!
mssql servermssql server 
Updated on 06-May-2013
Manoj Bhatt

Manoj Bhatt

Follow

Can you answer this question?

Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

06-May-2013
Hi Manoj!

You just set a default value in the new columns and that will allow you to add them.

alter table table_name
    add column_name datetime not null
       constraint DF_Default_Object_Name default (getdate())
or this one for a varchar field.

alter table table_name
    add column_name varchar(10) not null
       constraint DF_Default_Object_Name default ('A')
You can also drop the default if you do not need it after you added the column.

alter table table_name
    drop constraint DF_Default_Object_Name


Liked By