Sentry Answers>SQL Server>

How do I add a column with a default value to an existing table in SQL Server?

How do I add a column with a default value to an existing table in SQL Server?

Richard C.

—

The Problem

You want to add a column to an existing table in Microsoft SQL Server. How do you do it?

If you want the column to disallow null values, the ALTER TABLE statement will fail because all the existing rows will have no values for the new column.

Let’s use the following table of people as an example:

Click to Copy
CREATE TABLE Person
(
    Id INT PRIMARY KEY,
    Name VARCHAR(255)
);

INSERT INTO Person(Id, Name)
VALUES
    (1, 'Amir'),
    (2, 'Sofia'),
    (3, 'Aya'),
    (4, 'Mateo'),
    (5, 'Leila'),
    (6, 'Yara'),
    (7, 'Ndidi'),
    (8, 'Santiago');

How do we add a new column, Gender, with no null values allowed and a default value of Unspecified?

The Solution

If no null values are allowed for a new column, the solution is to specify a default value for the column. The query below will add the new Gender column:

Click to Copy
ALTER TABLE
    Person
ADD
    Gender VARCHAR(20) NOT NULL DEFAULT 'Unspecified';

However, it is neater to name both the constraints so you can refer to them by name if you want to change them in the future:

Click to Copy
ALTER TABLE
    Person
ADD
    Gender VARCHAR(20)
        CONSTRAINT cnstrt_not_null_gender NOT NULL
        CONSTRAINT cnstrt_default_gender DEFAULT 'Unspecified';

Here are the contents of the altered table:

Click to Copy
SELECT * FROM Person;
IdNameGender
1AmirUnspecified
2SofiaUnspecified
3AyaUnspecified
4MateoUnspecified
5LeilaUnspecified
6YaraUnspecified
7NdidiUnspecified
8SantiagoUnspecified

How To Allow Null Values

If you want to add a default value of Unspecified to existing rows and new rows, but still want to allow entering NULL for gender if explicitly specified, you can do this:

Click to Copy
ALTER TABLE
    Person
ADD
   Gender VARCHAR(20)
   CONSTRAINT cnstrt_default_gender DEFAULT 'Unspecified' WITH VALUES;

In this new ALTER statement, we no longer require Gender to be NOT NULL. We specify that a DEFAULT value is set only when no value is given, by the WITH VALUES phrase.

Now if we enter the following:

Click to Copy
INSERT INTO Person(Id, Name, Gender)
VALUES (20, 'Bob', NULL);

Bob will have NULL gender.

But if we enter:

Click to Copy
INSERT INTO Person(Id, Name)
VALUES (20, 'Bob');

Bob will have an Unspecified gender.

  • Syntax.fmListen to the Syntax Podcast
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

learn more about sentryjoin our discord
© 2024 • Sentry is a registered Trademark of Functional Software, Inc.