How to Add a Not Null Column Without Default Value

Can I add a not null column without DEFAULT value

No, you can't.

Because if you could, SQL wouldn't know what to put as value in the already existing records. If you didn't have any records in the table it would work without issues.

The simplest way to do this is create the column with a default and then remove the default.

ALTER TABLE dbo.MyTable ADD
MyColumn text NOT NULL CONSTRAINT DF_MyTable_MyColumn DEFAULT 'defaultValue'
ALTER TABLE dbo.MyTable
DROP CONSTRAINT DF_MyTable_MyColumn

Another alternative would be to add the column without the constraint, fill the values for all cells and add the constraint.

Add NOT NULL column without DEFAULT but WITH VALUES

Although you can't specify an ephemeral default constraint that's automatically dropped after adding the column (i.e. single statement operation), you can explicitly name the constraint to facilitate dropping it immediately afterward.

ALTER TABLE dbo.items
    ADD description VARCHAR(255) NOT NULL
    CONSTRAINT DF_items_description DEFAULT 'no-description' WITH VALUES;
ALTER TABLE dbo.items
    DROP CONSTRAINT DF_items_description;

Explict constraint names are a best practice, IMHO, as it makes subsequent DDL operations easier.

Add NOT NULL field without default value into a populated DB

You have to make sure that the other table has the my_field value for each entry in my_table.

class AddMyFieldToMyTable < ActiveRecord::Migration
  def up
    add_column :my_table, :my_field, :text
    execute("insert into my_table(my_field) values (select my_field from different_table where my_table.id = different_table.different_id)")
    change_column :my_table, :my_field, :text, null: false
  end

  def down
    remove_column :my_table, :my_field
  end
end

Add a column not null without default

I found this query that suits to me, it seems fine for me. Anyone tried this before ?

I add the column as NULL , then I modify it to not null.

alter table TEST_EMPLOYEE ADD COMP_CODE NUMERIC(4) NULL

alter table TEST_EMPLOYEE MODIFY COMP_CODE NUMERIC(4) NOT NULL

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;

MySQL add a NOT NULL column

In MySQL, each column type has an "implicit default" value.

For string types [the implicit] default value is the empty string.

If a NOT NULL column is added to a table, and no explicit DEFAULT is specified, the implicit default value is used to populate the new column data¹. Similar rules apply when the DEFAULT value is specified.

As such, the original DDL produces the same results as:

-- After this, data will be the same, but schema has an EXPLICIT DEFAULT
ALTER TABLE t ADD c varchar(10) NOT NULL DEFAULT ''
-- Now we're back to the IMPLICIT DEFAULT (MySQL stores NULL internally)
ALTER TABLE t ALTER c DROP DEFAULT

The "strict" mode settings affects DML statements relying on default values, but do not affect the implicit default usage when the column is added.

For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column [and if] strict SQL mode is enabled, an error occurs ..

Here is an sqlfiddle "proof" that strict mode does not apply to the ALTER TABLE .. ADD statement.

---

¹ This is a MySQL feature. Other engines, like SQL Server, require an explicit DEFAULT (or NULL column) constraint for such schema changes.

Unable to add a NOT NULL column to empty table in SQL Server

SQL Server 2000 does not check for an empty table. What you are seeing is an improvement in SQL Server 2005/2008.

Either of the following two step processes will make the change in SQL Server 2000 against an empty table:

ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) NOT NULL CONSTRAINT ActInv_Temp DEFAULT 'foo'
ALTER TABLE [ActInv] DROP CONSTRAINT ActInv_Temp

go

ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) NULL 
ALTER TABLE [ActInv] ALTER COLUMN [BATCHNUMBER] NVARCHAR(50) NOT NULL 

Change a Nullable column to NOT NULL with Default Value

I think you will need to do this as three separate statements. I've been looking around and everything i've seen seems to suggest you can do it if you are adding a column, but not if you are altering one.

ALTER TABLE dbo.MyTable
ADD CONSTRAINT my_Con DEFAULT GETDATE() for created

UPDATE MyTable SET Created = GetDate() where Created IS NULL

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL 

---

Related Topics