• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Adding new NOT NULL filed to existing table with existing data

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')
BEGIN
        ALTER TABLE tblPgVer
		ADD tePubQueueStep int NOT NULL
		ALTER TABLE tePubQueueStep ADD CONSTRAINT
		DF_tblPgVer_tePubQueueStep DEFAULT '1' FOR tePubQueueStep
END

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?
0
sonic1234
Asked:
sonic1234
1 Solution
 
_agx_Commented:
Try

ALTER TABLE tblPgVer
    ADD tePubQueueStep int NOT NULL
    CONSTRAINT DF_tblPgVer_tePubQueueStep
            DEFAULT 1 WITH VALUES  
0
 
sonic1234Author Commented:
Thank you agx
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now