Как добавить столбец со значением по умолчанию в SQL Server

Добавление столбца со значением по умолчанию в существующую таблицу в SQL Server 2000/2005

Чтобы добавить столбец со значением по умолчанию в существующую таблицу в SQL Server 2000/2005, используется команда ALTER TABLE ADD с ключевым словом DEFAULT. Базовый синтаксис: ALTER TABLE TableName ADD ColumnName DataType DEFAULT DefaultValue, а для столбцов с ограничением NOT NULL следует включать предложение WITH VALUES, чтобы применить значение по умолчанию к существующим строкам. Этот подход работает в обеих версиях SQL Server 2000 и 2005, хотя вы также можете создавать явно именованные ограничения для лучшего контроля и управления.

Содержание

• Базовый синтаксис для добавления столбцов со значениями по умолчанию
• Добавление столбцов с ограничениями NOT NULL
• Создание именованных ограничений по умолчанию
• Работа с различными типами данных
• Изменение существующих значений по умолчанию

Базовый синтаксис для добавления столбцов со значениями по умолчанию

Основной подход к добавлению столбца со значением по умолчанию в SQL Server 2000/2005 использует оператор ALTER TABLE в сочетании с ключевым словом DEFAULT. Этот синтаксис прост и работает как для столбцов, допускающих NULL, так и для столбцов с ограничением NOT NULL.

ALTER TABLE Employees ADD IsActive BIT DEFAULT 1;

В этом примере добавляется новый столбец с именем IsActive в таблицу Employees с типом данных BIT и устанавливается значение по умолчанию 1. При вставке новых строк без указания значения для этого столбца будет автоматически применяться значение по умолчанию 1.

Для строковых значений необходимо заключать их в одинарные кавычки:

ALTER TABLE Products ADD Status VARCHAR(20) DEFAULT 'Active';

Для значений даты можно использовать функции, такие как GETDATE():

ALTER TABLE Orders ADD CreatedDate DATETIME DEFAULT GETDATE();

Добавление столбцов с ограничениями NOT NULL

При добавлении столбца с ограничением NOT NULL и значением по умолчанию необходимо включить предложение WITH VALUES в SQL Server 2005 (в SQL Server 2000 это необязательно). Это важно, поскольку SQL Server должен знать, как обрабатывать существующие строки в таблице.

ALTER TABLE Customers ADD IsActive BIT NOT NULL DEFAULT 1 WITH VALUES;

Без предложения WITH VALUES SQL Server попытается установить для нового столбца значение NULL для всех существующих строк, что приведет к ошибке, поскольку столбец определен как NOT NULL. Предложение WITH VALUES указывает SQL Server применить значение по умолчанию ко всем существующим строкам.

Вот еще один пример со строковым типом данных:

ALTER TABLE Products ADD Category VARCHAR(50) NOT NULL DEFAULT 'General' WITH VALUES;

Создание именованных ограничений по умолчанию

Хотя SQL Server автоматически генерирует имена для ограничений по умолчанию при использовании простого синтаксиса, часто лучше явно именовать ваши ограничения. Это делает их более удобными для ссылки при необходимости изменения или удаления их позже.

Синтаксис для создания именованного ограничения по умолчанию:

ALTER TABLE TableName ADD CONSTRAINT ConstraintName DEFAULT DefaultValue FOR ColumnName;

Например:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_IsActive DEFAULT 1 FOR IsActive;

Это создает ограничение по умолчанию с именем DF_Employees_IsActive для столбца IsActive. В соглашениях об именовании обычно используется префикс, такой как DF_ (Default), за которым следует имя таблицы и имя столбца.

Как объясняется на Stack Overflow, “знание точного имени полезно при написании скриптов DROP-CREATE”, потому что вы можете ссылаться на ограничение по его явному имени, а не полагаться на автоматически сгенерированные имена, которые могут различаться в разных средах.

Работа с различными типами данных

Различные типы данных требуют специального форматирования для их значений по умолчанию. Вот примеры для распространенных типов данных:

Числовые типы:

ALTER TABLE Products ADD Price DECIMAL(10,2) DEFAULT 0.00;
ALTER TABLE Orders ADD Quantity INT DEFAULT 1;

Строковые типы:

ALTER TABLE Customers ADD Country VARCHAR(50) DEFAULT 'USA';
-- Для одинарных кавычек внутри строк удваиваем их:
ALTER TABLE Products ADD Description NVARCHAR(100) DEFAULT 'Standard ''Basic'' model';

Типы даты/времени:

ALTER TABLE Orders ADD OrderDate DATETIME DEFAULT GETDATE();
ALTER TABLE Products ADD CreatedDate SMALLDATETIME DEFAULT CURRENT_TIMESTAMP;

Булевы типы:

ALTER TABLE Users IsActive BIT DEFAULT 1;
ALTER TABLE Tasks IsCompleted BIT DEFAULT 0;

Специальные случаи:
Для значений NULL в качестве значений по умолчанию указывать DEFAULT NULL не нужно:

ALTER TABLE Customers ADD MiddleName VARCHAR(50) NULL;

Изменение существующих значений по умолчанию

Если вам нужно изменить значение по умолчанию для существующего столбца, сначала необходимо удалить существующее ограничение, а затем создать новое. Согласно MSSQLTips, “вы не можете изменить DEFAULT с помощью синтаксиса ALTER TABLE…ALTER COLUMN. Вместо этого вы должны удалить DEFAULT так же, как и другие объекты ограничений, и повторно добавить ограничение DEFAULT с новым значением по умолчанию.”

Вот процесс:

1. Сначала найдите имя ограничения (можно запросить sys.default_constraints или использовать SSMS для просмотра свойств таблицы)
2. Удалите существующее ограничение:

ALTER TABLE Employees DROP CONSTRAINT DF_Employees_IsActive;

3. Создайте новое ограничение с обновленным значением по умолчанию:

ALTER TABLE Employees ADD CONSTRAINT DF_Employees_IsActive DEFAULT 0 FOR IsActive;

Альтернативно, если вы добавляете новый столбец для замены существующего с другим значением по умолчанию:

-- Добавляем новый столбец с новым значением по умолчанию
ALTER TABLE Employees ADD IsActiveNew BIT DEFAULT 0;
-- Обновляем существующие данные
UPDATE Employees SET IsActiveNew = IsActive;
-- Удаляем старый столбец (при необходимости)
ALTER TABLE Employees DROP COLUMN IsActive;
-- Переименовываем новый столбец
EXEC sp_rename 'Employees.IsActiveNew', 'IsActive', 'COLUMN';

Источники

1. Adding a Default Value Column to an Already-Existing Table in SQL Server 2000/2005 - Medium
2. How to Add a Column with a Default Value to an Existing Table in SQL Server - GeeksforGeeks
3. How to add a column with a default value to an existing table in SQL Server - Stack Overflow
4. SQL Server: Add a Column with Default Value - PopSQL
5. How to Add a Default Value to a Column in SQL Server - DataReportive
6. Working with Default Constraints in SQL Server - MSSQLTips
7. Specify default values for columns - Microsoft Learn

Заключение

Добавление столбцов со значениями по умолчанию в SQL Server 2000/2005 является простой задачей с использованием команды ALTER TABLE ADD с ключевым словом DEFAULT. Помните, что при добавлении столбцов с ограничением NOT NULL следует использовать предложение WITH VALUES, чтобы существующие строки были правильно заполнены. Для лучшего обслуживания базы данных рекомендуется явно именовать ваши ограничения по умолчанию, а не полагаться на автоматически сгенерированные имена. При изменении существующих значений по умолчанию всегда сначала удаляйте старое ограничение перед созданием нового. Эти методы обеспечивают гибкость для эволюции схемы базы данных, сохраняя целостность и согласованность данных в вашей среде SQL Server.