Adding new column to existing table with default value

Question

Suppose I have a table called SerialNumbers which has columns:

Id (int)
SerialNumber (nvarchar)
IsValid (bit)
ToBeUsedBy (DateTime)

Currently this table is being used by numerous different applications. Now we have a requirement to add a new column named CurrentlyAt (int) which will always have a default value of 1. I have gone ahead and added the column with a default value of 1.

So my question is, would I now have to change all my previous insert queries to include the new column or would it add a value by default if I didn't add it to the old insert statements?

What would be the point of having a default if you were required to always supply a value anyway? ...no wait, that's a silly question because that's actually exactly how T-SQL default function parameters work (if you want the default, you still have to pass the parameter, but you can use default). Fortunately for INSERT statements the approach that's not useless prevails, and you're not required to mention the column at all. — Jeroen Mostert
– Jeroen Mostert, Commented May 2, 2019 at 13:57
@JeroenMostert Only one of the applications should be updating the value the rest do not need to supply it. The idea is when an entry is added to the table from any of the apps the default value is insert but when I try the sql query falls over because of the newly added column — Izzy
– Izzy, Commented May 2, 2019 at 14:00
You have to make the column NOT NULL for the default value to get populated if you don't specify the column in the insert. If you don't make the column NOT NULL it will just be NULL when the column is not in the insert statement. — Sean Lange
– Sean Lange, Commented May 2, 2019 at 14:03
@SeanLange: no, that's not how it works. Minimal repro: CREATE TABLE T(ID INT, X BIT NULL DEFAULT 0); INSERT T(ID) VALUES (1); SELECT * FROM T. You may be thinking of ORMs like Entity Framework who may get confused and retrieve the column for purposes of generating a statement, but without retrieving the default from the constraint (and so unwittingly pass an explicit NULL when the user did not intend it). — Jeroen Mostert
– Jeroen Mostert, Commented May 2, 2019 at 14:06

Eric Brandt · Accepted Answer · 2019-05-02 14:04:05Z

2

The answer, of course, is that it depends.

As long as your existing INSERT statements have an explicit column list, you don't need to edit them, and the default value will be inserted, well, by default.

INSERT INTO SerialNumbers (
  Id,
  SerialNumber,
  IsValid,
  ToBeUsedBy)
SELECT....

BUT if you have any inserts that do not have a column list, they'll throw an error:

INSERT INTO SerialNumbers 
SELECT....

Because the number of columns in the SELECT, or VALUES clause perhaps, will no longer match the number of columns in the INSERT target table.

The solution there is to add the explicit column list, and then to get in the habit of always using said list, for this very reason.

EDIT: To Sean Lange's point, I was assuming the new column was NOT NULL. If it's nullable, then yes, there are more possible outcomes, which are likely to generate outcomes that are not as expected.

answered May 2, 2019 at 14:04

Eric Brandt

8,1313 gold badges20 silver badges39 bronze badges

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

1 Comment

Izzy Over a year ago

That makes sense

Collectives™ on Stack Overflow

Adding new column to existing table with default value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related