Add Non-Nullable Columns to an Existing Table in SQL Server

insert a NOT NULL column to an existing table

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;

Safely add a non-null column to an existing table

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.

Alternate table with new not null Column in existing table in SQL

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>

Adding a new NOT NULL column to an existing Table with data

This piece of SQL is not valid in MySQL:

ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

Instead, consider :

ALTER TABLE `mytable` MODIFY newCol BIT NOT NULL;

Reference : MySQL ALTER TABLE syntax

How to add a new column on NOT NULL constraint

If the new row is supposed to be NOT NULL, add a DEFAULT clause to the column definition:

ALTER TABLE tab
ADD COLUMN Col3 INTEGER NOT null
DEFAULT 0;

Alternatively, omit the NOT NULL, fill the new column with UPDATE, then change the column to be NOT NULL:

ALTER TABLE tab
ALTER col3 SET NOT NULL;

After an UPDATE on the whole table, you should run VACUUM (FULL) tab to get rid of the bloat.

How to add not null unique column to existing table

1. Add not null column with some default.
2. Update column to be a sequential integers (see row_number() function)
3. Add UNIQUE constraint or UNIQUE index over new column

You can add IDENTITY column to a table (but from question it is not clear if you need it or not).

Can I add nullable column to an existing table in SQL Server

You can simply execute the following command:

ALTER TABLE ExistingTable ADD NullableColumnName DataType NULL

If the data type is INT, and the new column name is NewColumn, your command would be;

ALTER TABLE ExistingTable ADD NewColumn INT NULL

Adding a non-nullable column to existing table fails. Is the value attribute being ignored?

Short answer

The "value" attribute will not work if you are adding a not-null constraint at the time of the column creation (this is not mentioned in the documentation). The SQL generated will not be able to execute.

Workaround

The workaround described in the question is the way to go. The resulting SQL will be:

1. Add the column

   ALTER TABLE layer ADD COLUMN abstract_trimmed varchar(455);
   

2. Set it to a non-null value for every row

   UPDATE table SET abstract_trimmed = 'No text';
   

3. Add the NOT NULL constraint

   ALTER TABLE layer ALTER COLUMN abstract_trimmed SET NOT NULL;
   

Why?

A column default is only inserted into the column with an INSERT. The "value" tag will do that for you, but after the column is added. Liquibase tries to add the column in one step, with the NOT NULL constraint in place:

ALTER TABLE layer ADD abstract_trimmed VARCHAR(455) NOT NULL;

... which is not possible when the table already contains rows. It just isn't smart enough.

Alternative solution

Since PostgreSQL 8.0 (so almost forever by now) an alternative would be to add the new column with a non-null DEFAULT:

ALTER TABLE layer
ADD COLUMN abstract_trimmed varchar(455) NOT NULL DEFAULT 'No text';

The manual:

When a column is added with ADD COLUMN and a non-volatile DEFAULT is
specified, the default is evaluated at the time of the statement and
the result stored in the table's metadata. That value will be used for
the column for all existing rows. If no DEFAULT is specified, NULL is
used. In neither case is a rewrite of the table required.

Adding a column with a volatile DEFAULT or changing the type of an
existing column will require the entire table and its indexes to be
rewritten. As an exception, when changing the type of an existing
column, if the USING clause does not change the column contents and
the old type is either binary coercible to the new type or an
unconstrained domain over the new type, a table rewrite is not needed;
but any indexes on the affected columns must still be rebuilt. Table
and/or index rebuilds may take a significant amount of time for a
large table; and will temporarily require as much as double the disk space.

---

Related Topics