Can I add a not null column without DEFAULT value

Question

Can I add a column which is I specify as NOT NULL,I don't want to specify the DEFAULT value but MS-SQL 2005 says:

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'test' cannot be added to non-empty table 'shiplist' because it does not satisfy these conditions.

If YES, please let me know the syntax, if No please specify the reason.

I think the point of this is so that the column can be added as 'NOT NULL' but specify what the value should be for the existing columns and not having to do anything cute like creating a constraint then dropping it. Seems reasonable to me to want to have the NOT NULL, no default, but just specify what the value should be for existing rows. — bunggo
– bunggo, Commented Oct 18, 2018 at 19:37

Gimly · Accepted Answer · 2014-05-22 06:40:14Z

126

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.

edited May 22, 2014 at 6:40

answered Oct 22, 2010 at 14:52

Gimly

6,2153 gold badges44 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scotty Jamison Over a year ago

Mariadb gave me a syntax error - it seems I can't name the default constraint with it. I had to instead do ALTER TABLE MyTable ADD MyColumn text NOT NULL DEFAULT 'defaultValue' followed by ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT

Paul Tomblin · Accepted Answer · 2010-10-22 14:52:24Z

34

Add the column to the table, update the existing rows so none of them are null, and then add a "not null" constraint.

answered Oct 22, 2010 at 14:52

Paul Tomblin

184k59 gold badges324 silver badges412 bronze badges

5 Comments

Damien_The_Unbeliever Over a year ago

This is a better solution than create/drop the default, if the logic for the new values is more complex than a simple constant.

Gennady Vanin Геннадий Ванин Over a year ago

Why, or how, is it possibly better to update (manually?) + changing to "not null, instead of "updating" automatically through default + changing to "no default" (dropping default)?

Damien_The_Unbeliever Over a year ago

@vgv8 - what if the new values in each row depend on other columns already in the row? You can't express that via a default constraint.

Gennady Vanin Геннадий Ванин Over a year ago

@Damien_The_Unbeliever, see my reply stackoverflow.com/questions/3997966/… . I had to provide a variant of am answer in order to gain the reason to engage better formatting

Phil Over a year ago

To learn how to add a "not null" constraint, check out stackoverflow.com/a/689766/138757

aluky · Accepted Answer · 2017-07-18 18:03:50Z

10

I use this approach to insert NOT NULL column without default value

ALTER TABLE [Table] ADD [Column] INT NULL
GO
UPDATE [Table] SET [Column] = <default_value>
ALTER TABLE [Table] ALTER COLUMN [Column] INT NOT NULL

answered Jul 18, 2017 at 18:03

aluky

5516 silver badges12 bronze badges

Comments

Damien_The_Unbeliever · Accepted Answer · 2010-10-22 14:50:12Z

9

No - SQL Server quite reasonably rejects this, because it wouldn't know what value existing rows should have

It's easy to create a DEFAULT at the same time, and then immediately drop it.

answered Oct 22, 2010 at 14:50

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Comments

abatishchev · Accepted Answer · 2010-10-22 14:54:03Z

1

No.

Just use empty string '' (in case of character type) or 0 (if numeric), etc as DEFAULT value

answered Oct 22, 2010 at 14:54

abatishchev

101k88 gold badges303 silver badges443 bronze badges

Comments

chris · Accepted Answer · 2015-09-24 01:51:37Z

1

No you cannot. But you can consider to specify the default value to ('')

answered Sep 24, 2015 at 1:51

chris

111 bronze badge

Comments

CodeMonkey · Accepted Answer · 2020-09-13 08:20:58Z

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

answered Sep 13, 2020 at 8:20

CodeMonkey

12.6k38 gold badges130 silver badges252 bronze badges

Comments

Will Marcouiller · Accepted Answer · 2010-10-22 14:59:09Z

0

No, you can't, as SQL Server, or any other database engines will force this new column to be null for existing rows into your data table. But since you do not allow a NULL, you are required to provide a default value in order to respect your own constraint. This falls under great sense! The DBE will not extrapolate a value for non-null values for the existing rows.

answered Oct 22, 2010 at 14:59

Will Marcouiller

24.3k26 gold badges100 silver badges166 bronze badges

1 Comment

onedaywhen Over a year ago

While what you say does make sense, it is not true of at least one "database engine" being MS Access, which allows a NOT NULL column to be added to a table without also specifying a DEFAULT, the result of which is null values in a NOT NULL column. I'll provide proof as an answer...

Community · Accepted Answer · 2017-05-23 11:54:51Z

@Damien_The_Unbeliever's comment , Is it adding computed column? Neither question nor answer implied anything like that. In case of computed column the error states:

"Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted"

OK, if to continue this guessing game, here is my script illustrating the adding of "NOT NULL" column in one "ALTER TABLE" step:

CREATE TABLE TestInsertComputedColumn 
(
    FirstName VARCHAR(100),
    LastName CHAR(50)
);  

insert into TestInsertComputedColumn(FirstName,LastName)
     select 'v', 'gv8';
select * from TestInsertComputedColumn;

ALTER TABLE TestInsertComputedColumn 
      ADD FullName As FirstName + LastName PERSISTED NOT NULL;

select * from TestInsertComputedColumn;
--drop TABLE TestInsertComputedColumn;

Joee · Accepted Answer · 2020-07-07 11:02:27Z

-1

I used below approach it worked for me

Syntax:
ALTER TABLE <YourTable> ADD <NewColumn> <NewColumnType> NOT NULL DEFAULT <DefaultValue>

Example:
ALTER TABLE Tablename ADD ColumnName datetime NOT NULL DEFAULT GETDATE();

answered Jul 7, 2020 at 11:02

Joee

2,05422 silver badges21 bronze badges

Comments

Jeremy Caney · Accepted Answer · 2024-06-11 02:59:41Z

-1

ALTER TABLE MyTable ADD COLUMN MyColumn VARCHAR(20) NOT NULL

edited Jun 11, 2024 at 2:59

Jeremy Caney

7,808115 gold badges57 silver badges86 bronze badges

answered Jun 10, 2024 at 16:37

Tushar Patil

1

2 Comments

Jeremy Caney Over a year ago

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation?

NotTheDr01ds Over a year ago

Welcome to Stack Overflow! Your answer could be improved with additional supporting information. If possible, please edit your post to add further details, such as explanations or documentation, so that others can confirm that your answer is correct. You can find more information in How do I write a good answer? - "Brevity is acceptable, but fuller explanations are better." It might be helpful to review some highly upvoted answers as examples to follow. Thanks!

Collectives™ on Stack Overflow

Can I add a not null column without DEFAULT value

11 Answers 11

1 Comment

5 Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

1 Comment

5 Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related