+- 0Day Forums (https://zeroday.vip)
+-- Forum: Coding (https://zeroday.vip/Forum-Coding)
+--- Forum: Database (https://zeroday.vip/Forum-Database)
+---- Forum: Microsoft SQL Server (https://zeroday.vip/Forum-Microsoft-SQL-Server)
+---- Thread: insert a NOT NULL column to an existing table (/Thread-insert-a-NOT-NULL-column-to-an-existing-table)
insert a NOT NULL column to an existing table - stalinismhmy - 07-31-2023
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
RE: insert a NOT NULL column to an existing table - Drmuff9 - 07-31-2023
The error message is quite descriptive, try:
ALTER TABLE MyTable ADD Stage INT NOT NULL DEFAULT '-';
RE: insert a NOT NULL column to an existing table - Sirconemaking647 - 07-31-2023
ALTER TABLE `MY_TABLE` ADD COLUMN `STAGE` INTEGER UNSIGNED NOT NULL AFTER `PREV_COLUMN`;
RE: insert a NOT NULL column to an existing table - iwonatswubzkm - 07-31-2023
[Other SQL implementations have similar restrictions.][1] 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.
[1]:
[To see links please register here]
RE: insert a NOT NULL column to an existing table - wildee907 - 07-31-2023
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
RE: insert a NOT NULL column to an existing table - phototherapeutics804300 - 07-31-2023
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;
RE: insert a NOT NULL column to an existing table - valariasopjgda - 07-31-2023
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][1] since 2012
[1]:
[To see links please register here]
RE: insert a NOT NULL column to an existing table - Sirvolcanology8 - 07-31-2023
Alter TABLE 'TARGET' add 'ShouldAddColumn' Integer Not Null default "0"
RE: insert a NOT NULL column to an existing table - enamored33976 - 07-31-2023
**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):
```sql
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