Adding new NOT NULL filed to existing table with existing data

Posted on 2011-05-05
Last Modified: 2012-05-11
Hello Experts,

I am trying to construct a Query that will allow me to add a new INT field to an existing table, eg.

IF NOT EXISTS(select column_name from information_schema.columns
WHERE table_name = 'tblPgVer' AND column_name='tePubQueueStep')
        ALTER TABLE tblPgVer
		ADD tePubQueueStep int NOT NULL
		DF_tblPgVer_tePubQueueStep DEFAULT '1' FOR tePubQueueStep

Open in new window

However I am getting the following error;

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'tePubQueueStep' cannot be added to non-empty table 'tblPgVer' because it does not satisfy these conditions.

Could somebody please provide edits to the query that will allow me to do this.  Do I need to first add the filed allowing nulls, then set the value of 1 to all blank vales, then add the constriant?
Question by:sonic1234
    LVL 51

    Accepted Solution


    ALTER TABLE tblPgVer
        ADD tePubQueueStep int NOT NULL
        CONSTRAINT DF_tblPgVer_tePubQueueStep
                DEFAULT 1 WITH VALUES  

    Author Closing Comment

    Thank you agx

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now