I have tried:
ALTER TABLE MY_TABLE
ADD STAGE INT NOT NULL;
But it gives this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified
This question is related to
sql-server
alter-table
notnull
Alter TABLE 'TARGET' add 'ShouldAddColumn' Integer Not Null default "0"
The error message is quite descriptive, try:
ALTER TABLE MyTable ADD Stage INT NOT NULL DEFAULT '-';
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
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
ALTER TABLE `MY_TABLE` ADD COLUMN `STAGE` INTEGER UNSIGNED NOT NULL AFTER `PREV_COLUMN`;
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.
Source: Stackoverflow.com