How to Add a NOT NULL column in an existing table? Is it possible, seems not!!! What’s your reaction? Let’s try out

Let stimulate the whole scenario, by taking a table NotNullColumnDemo with two column firstName and lastName

create table NotNullColumnDemo

( firstName varchar(10),

  lastName varchar(10))

Command(s) completed successfully.

Lets insert two row in the table :

insert into NotNullColumnDemo values ('Mythri','Patel')

insert into NotNullColumnDemo values ('Mansha','Patnaik')

(1 row(s) affected)

(1 row(s) affected)

select * from NotNullColumnDemo

firstName  lastName

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

Mythri     Patel

Mansha     Patnaik

(2 row(s) affected)

Now let’s try to add a column salary with a NOT NULL constraint as what our requirement is

alter table NotNullColumnDemo

add salary numeric(7,0) not null

OUTPUT

Msg 4901, Level 16, State 1, Line 1

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 'salary' cannot be added to non-empty table 'NotNullColumnDemo' because it does not satisfy these conditions.

Opps!!! Why it is not allowing me to add a new column with NOT NULL constraint???

Reason: Simply, you can't. Because if you could SQL wouldn't know what to put as value in the already existing records. If you don't have any records in the table it would work by the way.

So What to do now, because Manager needs no justification, he want me to do, cant to done work??

Crook Solution: The best way to do this is create the column with a default and then remove the default constraint. Lets fuel conceptual solution through t-sql

alter table NotNullColumnDemo

add salary numeric(7,0) not null

constraint df_salaray default 49000

Command(s) completed successfully.

select * from NotNullColumnDemo

firstName  lastName   salary

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

Mythri     Patel      49000

Mansha     Patnaik    49000

(2 row(s) affected)

alter table NotNullColumnDemo

drop constraint df_salaray

Command(s) completed successfully.

Now you have a column salary with NOT NULL constraint, you can now update the existing record salary column with the correct salary amount.  

Coding is cool, let’s take pleasure in it                 

Post Reference: Vikram Aristocratic Elfin Share