none
Adding NOT NULL DEFAULT VALUE column to existing table with data

    Question

  • I try to add a bit column to a table. I would like this to take a default value of 0/False.

    I am warned by SQl Server Manager Studio that this will cause a table recreate. The table is small but it is a master table witch cascading delete to other tables - so I am afraid that this will cause deletion in other tables.

    So my question is: How can I add a new column with not null/default value without triggering referential integrity

    TIA


    Best regards Soeren D.

    Friday, July 13, 2012 1:48 PM

Answers

All replies

  • Are you trying to add the column in the middle of the table?

    You should just be able to add the column like this:


    ALTER TABLE   tablename  ADD
     bitColumn bit NOT NULL CONSTRAINT DF_tablename_bitColumn DEFAULT 0




    Chuck Pedretti | Magenic – North Region | magenic.com


    Friday, July 13, 2012 1:52 PM
  • Do not do it using the UI or table designer, and use plain T-SQL (DDL) as Chuck suggested.


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Soeren Dalby Monday, July 16, 2012 7:04 AM
    Friday, July 13, 2012 1:57 PM
  • Hi Chuck and HunchBack

    No it is appended to the list of columns. Will it work if I use TQSL?

    Next question then: Can I do something similar for fields already added to existing tables (switching to not null)? The null able types are bad for my Dbml model.

    TIA


    Best regards Soeren D.

    Friday, July 13, 2012 2:05 PM
  • Just make sure that the column does not contain NULL values.

    CREATE TABLE Test (AColumn INT NULL);
    ALTER TABLE Test ALTER COLUMN AColumn INT NOT NULL;

    • Marked as answer by Soeren Dalby Monday, July 16, 2012 7:04 AM
    Friday, July 13, 2012 2:12 PM