Блог

Я использую SQL Server 2017 и хочу добавить NOT NULL столбец без DEFAULT , но указать значения для текущей записи, например, используя WITH VALUES в одном запросе.

Позвольте мне объяснить. Я понимаю тот факт, что я не могу создать NOT NULL столбец без указания значений. Но DEFAULT предложение устанавливает значение по умолчанию для этого столбца также для будущих вставок, которые я не хочу. Я хочу, чтобы значение по умолчанию использовалось только для добавления этого нового столбца, и все.

Позвольте мне объяснить. Предположим, что такая последовательность запросов:

 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
  

Последний запрос выдает ошибку (как и ожидалось):

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

Это так, потому что мы не указали значение для description столбца. Это очевидно.

Давайте рассмотрим ситуацию, когда в таблице items есть несколько записей. Без DEFAULT WITH VALUES предложений and это приведет к сбою (очевидно), поэтому давайте использовать их сейчас:

 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;
  

Итак, теперь наша таблица выглядит так, как и ожидалось:

 SELECT * FROM items;

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

Но с этого момента записи можно INSERT без 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 |
--------------------------------------
  

Но когда мы сравниваем это с нашей первой ситуацией (пустая таблица без DEFAULT предложения), она отличается. Я все еще хочу ошибку, касающуюся NULL description столбца and .

SQL Server создал ограничение по умолчанию для этого столбца, которое я не хочу иметь.

Решение состоит в том, чтобы либо удалить ограничение после добавления нового столбца с DEFAULT предложением, либо разделить добавление нового столбца на 3 запроса:

 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
  

Мой вопрос:

Есть ли способ достичь этого в одном запросе, но без создания константы по умолчанию?

Было бы неплохо, если бы можно было использовать значение по умолчанию только для запроса без постоянного создания ограничения.

Хотя вы не можете указать эфемерное ограничение по умолчанию, которое автоматически удаляется после добавления столбца (т. Е. Операция с одним оператором), Вы можете явно указать ограничение, чтобы облегчить его удаление сразу после этого.

 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;
  

Явные имена ограничений — лучшая практика, ИМХО, поскольку это упрощает последующие операции DDL.