insert a NOT NULL column to an existing table

Question

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

Motheaten

For PostgreSQL: https://mcmap.net/q/93027/-how-can-i-add-a-column-that-doesn-39-t-allow-nulls-in-a-postgresql-database/9157799 – Bottoms

Answer 1

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;

Elwoodelwyn

I personally prefer the first way here if you have values you can put in the field manually. That way you don't have to worry about creating and deleting a default constraint where you don't need one. – Cubicle

@MarkWDickson - The first one seems more dangerous to me. What happens if the wrong column slipped into the set statement: UPDATE MY_TABLE SET Employee_Salary = 0 – Centiare

@Centiare - I think that's reaching. The dangerous update statement you mention would be detrimental in any query. It should be simple enough to see if you have an extra column in the update statement here. You would generally be adding only a column or two at a time. If you happen to add an extra column into your update statement that doesn't belong there, in this example, then maybe you shouldn't be in charge of data in the first place. – Cubicle

@MarkWDickson - I would think that one of the duties of being in charge of data is to minimize change. It is not another update statement or column, it is the column name being incorrect. For example a database where there are two columns with similar names and the incorrect name is chosen. This is a statement that sets every row to some value (e.g. 0) for a particular column. Sometimes sweeping modifications need to be made and due care should be taken, but in this case there is an alternative, so why introduce the risk? – Centiare

ANDROID Developers using SQLite be aware that ALTER COLUMN is NOT supported in SQLite. – Suzan

I disagree with @MarkWDickson. I think the second one is dangerous. Adding a default value makes it possible to accidently insert rows where the new column is not specified. E.g. In a stored procedure, you forget to use @NewColValue, and you get no error... (It just happened here :P) – Genevagenevan

the use of GO was something i found that you HAVE to use. otherwise it throws an error – Mooncalf

@Genevagenevan I think something got crossed in communication. I agree with you that adding a default value to the column is not a suitable solution. When it comes to table structure, I find that adding defaults or cascading deletes (just another example) makes it far too easy to corrupt the data. – Cubicle

I've never seen GO before and it appears to be not a part of the SQL specification, thus it will probably lead to failure for scripts not being executed by one of the tools that support it. Just use semicolons? I don't recommend spreading Microsoft standards as they rarely care about any established and reasonable standard, but invent their own just to have their own invented. Apart from that, helpful answer. – Scammon

@Neon thanks for the comment, but original question was about MS SQL server, that is why GO is there. But I will add a note about it to my answer. – Elwoodelwyn

@PavelMorshenyuk Sorry, I didn't see that it's a question targeting MS SQL server. I checked that before I wrote my comment and couldn't find any reference to MS SQL and I came here googling a general SQL question. Today I found the sql-server tag, which appears to be linked to MS SQL server, although I find this very misleading as well, because a MySQL or Oracle SQL server is an SQL server for me as well. Thanks for adding a note, though. Maybe it will help another confused soul one day. – Scammon

Just to add clarity to the use of the keyword 'GO'... this is not part of Microsoft's SQL language either. It's a keyword that's only used within some of their tools such as SQLServer Studio, that provides a way to execute batches. It can be changed in the tool's options to something other than 'GO', such as 'FOO'. Just to be clear, it's not part of Microsoft's T-SQL language. If you are using other tools such as Squirrel-SQL or an ODBC connection to the database, the 'GO' keyword will throw an error. Hope this helps clarify things for folks. – Digest

second approach is easy, nice @PavelMorshenyuk – Gasparo

Answer 2

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

Rivkarivkah

You don't need to, but it is one option. – Pedometer

@Pedometer yes you do. If the table has any rows in it at all and given the condition stated "If you aren't allowing the column to be Null" then how else can the new column avoid violating the NOT NULL constraint for existing rows? – Rivkarivkah

another option is to add the column as nullable, then update every row in the table with an update statement, then alter the column to not-nullable. This way you're not left with a DEFAULT constraint that you likely don't want. – Pedometer

@Pedometer - this can be a lot slower, especially in the event that the method with a default is done as a metadata only change (i.e. default with a runtime constant value on Enterprise Edition or Azure SQL database). It is easy to drop the default if not desired going forward. Firstly the UPDATE needs to UPDATE all rows and then the altering to NOT NULL also does (somewhat surprisingly) dba.stackexchange.com/questions/29522/… – Rivkarivkah

Actually I just realised this was a delayed reply by over a year to the previous comment. In that case you are allowing the column to be NULL - albeit only temporarily. If you want to run an ALTER TABLE ADD column NOT NULL and the table has rows you need to provide a default – Rivkarivkah

That's like saying 1+1 and 1+2-1 are the same thing. Sure, they get you where you're going. – Escalator

Answer 3

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

Inamorato

Answer 4

The error message is quite descriptive, try:

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

Beam

and what's the meaning of the "minus" in the default part? – Lithophyte

All existing rows and future rows not specifying "Stage" will get "-" as the value. – Ongoing

@Ongoing but how would that work? is '-' some kind of a special/magic number in sql? i don't really understand why it would be a char instead of an int or is that char converted to an int? seems strange without an explanation – Stamata

@Stamata great question - I guess you need to try it on a test table. Wild guess would be either it will save the ascii number value for -, or save „-0“ (so 0), or error. Maybe the answered just wanted to tell you to think of a sane default and put it there. – Ongoing

Answer 5

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.

Entirety

MySQL supports adding non null columns into existing table with data, where the "sensible" empty value for the data type is supplied into the existing rows (i.e. 0.0 for float, 0 for integer, empty string for string, etc). – Iphigeniah

Answer 6

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

Newkirk

Answer 7

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

Kirov

Answer 8

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

Matthew

What does this answer add to this topic? – Alcuin