SQL Server Performance

Add NOT NULL column to existing table

Discussion in 'SQL Server 2005 General Developer Questions' started by sql_machine, Aug 29, 2006.

  1. sql_machine New Member

    Hi all,

    Is there a way to add a column that does not allow nulls and does not have a default to an existing table:

    alter TABLE [dbo].[MyTable]
    add MyColumn varchar (100) Not NULL
    GO

    Any help is appreciated.
  2. ndinakar Member

    No. Either you add the column with allowing NULL's, populate the data make sure there are no NULL values and then enfore the constraint.


    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  3. dineshasanka Moderator

    another way,
    Add a coumn with a default constraint

    ADD AddDate smalldatetime NOT NULL
    CONSTRAINT AddDateDflt
    DEFAULT getdate() WITH VALUES

    then drop the constraint

    Drop CONSTRAINT AddDateDflt


    ----------------------------------------

  4. sql_machine New Member

    that sounds more like it, because NOT NULL is a property,and will not allow me to change the column from NULL to NOT NULL. But I can create a default and then drop it, thanks!

Share This Page