insert a NOT NULL column to an existing table

I have tried:

ALTER TABLE MY_TABLE 
ADD STAGE INT NOT NULL;

But it gives this error message:

ALT开发者_运维知识库ER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified

---

As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:

ALTER TABLE MY_TABLE ADD STAGE INT NULL
GO
UPDATE MY_TABLE SET <a valid not null values for your column>
GO
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL
GO

Another option is to specify correct default value for your column:

ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL DEFAULT '0'

---

UPD: Please note that answer above contains GO which is a must when you run this code on Microsoft SQL server. If you want to perform the same operation on Oracle or MySQL you need to use semicolon ; like that:

ALTER TABLE MY_TABLE ADD STAGE INT NULL;
UPDATE MY_TABLE SET <a valid not null values for your column>;
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL;

---

If you aren't allowing the column to be Null you need to provide a default to populate existing rows. e.g.

ALTER TABLE dbo.YourTbl ADD
    newcol int NOT NULL CONSTRAINT DF_YourTbl_newcol DEFAULT 0

On Enterprise Edition this is a metadata only change since 2012

---

A faster solution

If you, like me, need to do this on a table with a large amount of data, the ADD-UPDATE-ALTER option is very slow (it can take hours for millions of rows).

If you also don't want a default value on your table, here's the full code for creating a column and dropping the default constraint (pretty much instant even for large tables):

ALTER TABLE my_table ADD column_name INT NOT NULL CONSTRAINT my_table_default_constraint DEFAULT 0
GO

ALTER TABLE my_table DROP CONSTRAINT my_table_default_constraint 
GO

This is for SQL Server

---

The error message is quite descriptive, try:

ALTER TABLE MyTable ADD Stage INT NOT NULL DEFAULT '-';

---

Other SQL implementations have similar restrictions. The reason is that adding a column requires adding values for that column (logically, even if not physically), which default to NULL. If you don't allow NULL, and don't have a default, what is the value going to be?

Since SQL Server supports ADD CONSTRAINT, I'd recommend Pavel's approach of creating a nullable column, and then adding a NOT NULL constraint after you've filled it with non-NULL values.

---

This worked for me, can also be "borrowed" from the design view, make changes -> right click -> generate change script.

BEGIN TRANSACTION
GO
ALTER TABLE dbo.YOURTABLE ADD
    YOURCOLUMN bit NOT NULL CONSTRAINT DF_YOURTABLE_YOURCOLUMN DEFAULT 0
GO
COMMIT

---

ALTER TABLE `MY_TABLE` ADD COLUMN `STAGE` INTEGER UNSIGNED NOT NULL AFTER `PREV_COLUMN`;

---

Alter TABLE 'TARGET' add 'ShouldAddColumn' Integer Not Null default "0"