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;
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
You will either have to specify a DEFAULT, or add the column with NULLs allowed, update all the values, and then change the column to NOT NULL.
ALTER TABLE <YourTable>
ADD <NewColumn> <NewColumnType> NOT NULL DEFAULT <DefaultValue>
Choose either:
a) Create not null with some valid default value
b) Create null, fill it, alter to not null
You just set a default value in the new columns and that will allow you to add them.
alter table table_name
add column_name datetime not null
constraint DF_Default_Object_Name default (getdate())
or this one for a varchar field.
alter table table_name
add column_name varchar(10) not null
constraint DF_Default_Object_Name default ('A')
You can also drop the default if you do not need it after you added the column.
alter table table_name
drop constraint DF_Default_Object_Name
If you don't want to place a default on the columns, you can:
- create the new columns as NULLable
- UPDATE the existing data appropriately
- add the NOT NULL constraint
You can use following syntax:
BEGIN TRANSACTION
ALTER TABLE [MySchema].[MyTable]
ADD [MyColumn] VARCHAR(10) NOT NULL CONSTRAINT MyTableDefault DEFAULT 'ValueForExistingData';
ALTER TABLE [MySchema].[MyTable]
DROP CONSTRAINT MyTableDefault;
COMMIT
On Enterprise edition SQL Server 2012+ both statements will be metadata only operations, so you will not have to update all data pages and the whole operation will take milliseconds.
You can set a Default constrain to ensure that all existing column has a value for existing data.
Then drop the default constrain to force new lines to have an entered value.
IF NOT EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N'MyColumn' AND Object_ID = Object_ID(N'[MySchema].[MyTable]'))
ALTER TABLE [MySchema].[MyTable]
ADD [MyColumn] VARCHAR(10) NOT NULL DEFAULT 'ValueForExistingData';
GO
ALTER TABLE [MySchema].[MyTable]
ALTER COLUMN [MyColumn] DROP DEFAULT;
GO
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql
Referencing the documentation for ALTER TABLE (highlighting mine),
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.
Also, Paul Randal has some good information about what happens when you add columns (and how long the operation might take) in Misconceptions around adding columns to a table. To summarize some main points:
- New column is nullable, with a NULL default. The table’s metadata records the fact that the new column exists but may not be in the record. This is why the null bitmap also has a count of the number of columns in that particular record. SQL Server can work out whether a column is present in the record or not. So – this is NOT a size-of-data operation – the existing table records are not updated when the new column is added. The records will be updated only when they are updated for some other operation.
New column is nullable, with a non-NULL default. It depends which version of SQL Server you’re using:
Before SQL Server 2012: This IS a size-of-data operation. The non-NULL default forces all existing records to be updated when the column is added, and so the null bitmap will be updated too.
SQL Server 2012 onward: same behavior as for a NULL default nullable column (i.e. metadata only operation)
- New column is not-nullable (obviously with a non-NULL default). This IS a size-of-data operation.
the entire column will be null for all the records in the table
begin tran
ALTER TABLE MyTable ADD MyColumn varchar(255) NULL;
select MyColumn,* from MyTable
rollback
run this command and check what happens!!!
the code inside rollback update temporily so u can check ,, by modifying thecode also