CODEWITHSUNDEEP
sqlsql-serverdatabase
sanjeev40084
sanjeev40084

Reputation: 9617

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

I already have a table which consists of data. I need to alter the 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):

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

  2. 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)

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

When I tried to save, this error message appeared:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

Upvotes: 50

Views: 44958

Answers (3)

Tony L.
Tony L.

Reputation: 19396

Adding new NOT NULL columns with defaults can be done in the GUI like below. Changing an existing to NOT NULL appears to be a different story. I get the same message you had. One option would be to create a new NOT NULL column with a default to replace the old column and then copy the old column data to the new column data.

  1. Put table in Design View (right click on table->select Design)
  2. Add column, select data type
  3. Uncheck Allow Nulls and set Default Value or Binding = your default values like below

enter image description here

Upvotes: 2

Jose Chama
Jose Chama

Reputation: 2978

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

Upvotes: 78

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

If you don't want to place a default on the columns, you can:

  1. create the new columns as NULLable
  2. UPDATE the existing data appropriately
  3. add the NOT NULL constraint

Upvotes: 27

Related Questions