Add NOT NULL column without DEFAULT but WITH VALUES

Question

I'm using SQL Server 2017 and I want to add a NOT NULL column without DEFAULT but supply a values for current record e.g. using WITH VALUES in a single query.

Let me explain. I understand the fact that I cannot create a NOT NULL column without supplying values. But a DEFAULT clause sets a default value for this column also for future inserts which I don't want. I want a default value to be used only for adding this new column and that's it.

Let me explain. Assume such a sequence of queries:

CREATE TABLE items (
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1)
);

ALTER TABLE items ADD name VARCHAR(255) NOT NULL; -- No default value because table is empty

INSERT INTO items(name) VALUES( 'test'); -- ERROR

Last query gives error (as expected):

Error: Cannot insert the value NULL into column 'description', table 'suvibackend.dbo.items'; column does not allow nulls. INSERT fails.

It is so because we didn't supply value for description column. It's obvious.

Let's consider a situation when there are some records in items table. Without a DEFAULT and WITH VALUES clauses it will fail (obviously) so let's use them now:

CREATE TABLE items (
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name varchar(255) NOT NULL
);

INSERT INTO items(name) VALUES ('name-test-1');
INSERT INTO items(name) VALUES ('name-test-2');

ALTER TABLE items ADD description VARCHAR(255) NOT NULL DEFAULT 'no-description' WITH VALUES;

So now our table looks like this as expected:

SELECT * FROM items;

--------------------------------------
| id  | name        | description    |
| --- | ----------- | -------------- |
| 1   | name-test-1 | no-description |
| 2   | name-test-2 | no-description |
--------------------------------------

But from now on, it is possible to INSERT records without description:

INSERT INTO items(name) VALUES ('name-test-3'); -- No description column

SELECT * FROM ITEMS;

--------------------------------------
| id  | name        | description    |
| --- | ----------- | -------------- |
| 1   | name-test-1 | no-description |
| 2   | name-test-2 | no-description |
| 3   | name-test-3 | no-description |
--------------------------------------

But when we compare this to our first situation (empty table without DEFAULT clause) it is different. I still want an error regarding NULL and description column.

SQL Server has created a default constraint for this column which I don't want to have.

The solution is to either drop a constraint after adding a new column with DEFAULT clause, or to split adding new column into 3 queries:

CREATE TABLE items 
(
    id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name varchar(255) NOT NULL
);

INSERT INTO items(name) VALUES ('name-test-1');
INSERT INTO items(name) VALUES ('name-test-2');

ALTER TABLE items 
    ADD description VARCHAR(255) NULL;

UPDATE items 
SET description = 'no description'


ALTER TABLE items 
    ALTER COLUMN description VARCHAR(255) NOT NULL;

INSERT INTO items(name) 
VALUES ('name-test-3'); -- ERROR as expected

My question:

Is there a way to achieve it in a single query, but without having a default constaint created?

It would be nice if it is possible to use a default value just for a query without permanently creating a constraint.

Answer 1

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.