how to add not null column in existing table and then insert values in that column?

Question

how to add not null column in existing table and then insert values in that column ?? in sql...

Which database server are you using? Microsoft SQL Server, MySql, etc? — Pascal Berger
– Pascal Berger, Commented Jan 10, 2016 at 10:12
If table has data, you can't. You have to add it as Null then populate the column for all rows and finally alter it to not null. — shadow
– shadow, Commented Jan 10, 2016 at 10:27
@shadow, as I've pointed out in my answer you can, if you specify a DEFAULT... — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 10, 2016 at 10:31

Gottfried Lesigang · Accepted Answer · 2016-01-10 10:24:49Z

3

If you want to add a NOT NULL column you must specify a DEFAULT:

ALTER TABLE YourTable 
    ADD SomeColumn INT NOT NULL 
    CONSTRAINT DF_YourTable_SomeColumn DEFAULT(0);

Other possibility is to add it with NULL, add your data and alter it to NOT NULL later (see ALTER TABLE)

EDIT: Your comment about "how to insert values"...

This depends very much in your needs. If you want to set all rows to the same value it is:

UPDATE YourTable SET SomeColumn=0;

edited Jan 10, 2016 at 10:24

answered Jan 10, 2016 at 10:14

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rahul Sirohi Over a year ago

then how to insert values in that column ??

Gottfried Lesigang Over a year ago

@RahulSirohi, edited my answer... This is absolute base knowledge actually. So please use one of the millions of tutorials you'll find out there...

Rahul Sirohi Over a year ago

if i want to add different values ?

Gottfried Lesigang Over a year ago

@RahulSirohi, really... How should I answer this without any knowledge about your data table and its content??? If there is a unique key (might be a primary key) you can set each row separately with UPDATE YourTable SET SomeColumn=aValue WHERE TheKey=123;

Collectives™ on Stack Overflow

how to add not null column in existing table and then insert values in that column?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related