ADD NON-NULLABLE COLUMNS TO AN EXISTING TABLE IN SQL SERVER?

Manoj Bhatt

Total Post:154

Points:1086
Posted by  Manoj Bhatt
 1487  View(s)
Ratings:
Rate this:
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!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: Add non-nullable columns to an existing table in sql server?

    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


Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!