CREATE DEFAULT (Transact-SQL)
Создает объект «Значение по умолчанию». Если этот объект привязан к столбцу или псевдониму типа данных, он указывает значение, которое должно вставляться в столбец (или во все столбцы псевдонима типа данных), если при вставке значение не задано явно.
В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Вместо них используйте определения значений по умолчанию, созданные с помощью ключевого слова DEFAULT инструкций ALTER TABLE и CREATE TABLE.
Синтаксис
CREATE DEFAULT [ schema_name . ] default_name AS constant_expression [ ; ]
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
schema_name
Имя схемы, которой принадлежит значение по умолчанию.
default_name
Имя значения по умолчанию. Имена значений по умолчанию должны соответствовать правилам для идентификаторов. Указывать имя владельца по умолчанию не обязательно.
constant_expression
Выражение, содержащее только постоянные значения (не может включать имена столбцов или других объектов баз данных). Вы можете использовать любые константы, встроенные функции или математические выражения, за исключением тех, которые содержат типы данных псевдонимов. Определяемые пользователем функции нельзя использовать. Константы символьного типа и даты необходимо заключать в одинарные кавычки (‘). Константы, имеющие тип денежных данных, а также целочисленные и с плавающей точкой в кавычки не заключаются. Двоичные данные должны сопровождаться знаком 0x, а денежные данные — знаком доллара ($). Тип значения по умолчанию должен соответствовать типу данных столбца.
Замечания
Имя по умолчанию может быть создано только в текущей базе данных. Внутри базы данных имена по умолчанию должны быть уникальны в схеме. Чтобы созданное значение по умолчанию привязать к типу данных столбца или псевдонима, используйте sp_bindefault.
Если значение по умолчанию не совместимо с форматом столбца, к которому оно привязывается, то SQL Server формирует сообщение об ошибке при попытке применить значение по умолчанию. Например, значение N/A не может быть использовано по умолчанию для столбца, содержащего числовые данные.
Если значение является слишком длинным для столбца, к которому оно привязывается, то происходит его усечение.
Инструкции CREATE DEFAULT не могут использоваться в одном пакете с другими инструкциями Transact-SQL.
Перед созданием нового значения по умолчанию необходимо удалить старое значение с таким же именем, предварительно удалив все его связи с помощью sp_unbindefault.
Каждому столбцу соответствуют значение по умолчанию и связанное с ним правило, причем значение по умолчанию должно соответствовать правилу. Если значение по умолчанию противоречит правилу, они никогда не будет применяться, а SQL Server будет создавать ошибку при каждой попытке вставить его.
При привязке к столбцу значение по умолчанию вставляется при следующих условиях.
- Значение вставляется неявным образом.
- При выполнении функции INSERT для вставки значений по умолчанию используются ключевые слова DEFAULT VALUES или DEFAULT.
Если при создании столбца было указано NOT NULL и не были созданы значения по умолчанию, то при попытке записи в данный столбец будет выдаваться сообщение об ошибке. В следующей таблице представлена связь между фактом существования значения по умолчанию и определением столбца как NULL или NOT NULL. Записи таблицы отображают результаты.
| Определение столбца | Нет записи, значение по умолчанию отсутствует | Нет записи, присвоено значение по умолчанию | Введено NULL, значение по умолчанию отсутствует | Введено NULL, значение по умолчанию |
|---|---|---|---|---|
| NULL | NULL | default | NULL | NULL |
| NOT NULL | Ошибка | default | error | error |
Чтобы переименовать значение по умолчанию, используйте sp_rename. Чтобы получить отчет о значении по умолчанию, используйте sp_help.
Разрешения
Чтобы использовать команду CREATE DEFAULT, пользователь должен обладать разрешением CREATE DEFAULT в текущей базе данных и разрешением ALTER на схему, в которой создается значение по умолчанию.
Примеры
А. Создание простого символьного значения по умолчанию
В следующем примере создается символьное значение по умолчанию с именем unknown .
USE AdventureWorks2022; GO CREATE DEFAULT phonedflt AS 'unknown'; B. Привязка значения по умолчанию
При выполнении следующего примера производится привязка созданного в примере A значения по умолчанию. Значение по умолчанию используется в случае, когда в столбце Phone таблицы Contact нет записи.
Пропуск записи отличается от явного указания значения NULL в инструкции INSERT.
Выполнение следующей инструкции Transact-SQL заканчивается сбоем, так как не существует значения по умолчанию с именем phonedflt . Данный пример служит только для демонстрационных целей.
USE AdventureWorks2022; GO sp_bindefault 'phonedflt', 'Person.PersonPhone.PhoneNumber'; Указание значений по умолчанию для столбцов
С помощью СРЕДЫ SQL Server Management Studio можно указать значение по умолчанию, которое будет введено в столбец таблицы. По умолчанию можно задать с помощью обозревателя объектов SSMS или выполнения Transact-SQL.
Если значение по умолчанию не задано столбцу и пользователь оставляет столбец пустым, происходит следующее:
- если активирована поддержка значений NULL, в столбец вставляется значение NULL ;
- если поддержка значений NULL не активирована, столбец остается пустым, но пользователь не сможет сохранить строку, пока не предоставит какое-либо значение.
Ограничения
Перед началом работы необходимо учесть следующие ограничения:
- Если данные, введенные в поле Значение по умолчанию , заменяют связанное со столбцом значение по умолчанию (которое отображается без скобок), то будет предложено отменить привязку значения по умолчанию и заменить его новым значением.
- При вводе текстовых строк заключайте их в одинарные кавычки (‘); не используйте двойные кавычки («), потому что они зарезервированы для идентификаторов.
- Чтобы задать численное значение по умолчанию, введите число без одинарных кавычек.
- Чтобы задать объект или функцию, введите имя объекта или функции без двойных кавычек.
В Azure Synapse Analytics для ограничения по умолчанию можно использовать только константы. Выражение нельзя использовать с ограничением по умолчанию.
Разрешения
Для выполнения действий, описанных в этой статье, требуется разрешение ALTER для таблицы.
Использование SSMS для указания значения по умолчанию
Обозреватель объектов в SSMS можно использовать для указания значения по умолчанию для столбца таблицы. Для этого выполните следующие шаги:
- Подключитесь к экземпляру SQL Server в SSMS.
- В обозревателе объектов щелкните правой кнопкой мыши таблицу со столбцами, масштаб которых необходимо изменить, и выберите Конструктор.
- Выберите столбец, для которого нужно задать значение по умолчанию.
- На вкладке Свойства столбца введите новое значение по умолчанию в свойстве Значение по умолчанию или привязка .
Заметка Чтобы задать численное значение по умолчанию, введите число. В случае объекта или функции нужно ввести его или ее имя. Чтобы задать алфавитно-цифровое значение по умолчанию, введите его, заключив в одинарные кавычки.
Использование Transact-SQL для указания значения по умолчанию
Существуют различные способы указания значения по умолчанию для столбца с помощью отправки T-SQL.
ALTER TABLE (T-SQL)
- В обозревателе объектов подключитесь к экземпляру ядра СУБД.
- На стандартной панели выберите пункт Создать запрос.
- Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.
CREATE TABLE dbo.doc_exz (column_a INT, column_b INT); -- Allows nulls. GO INSERT INTO dbo.doc_exz (column_a) VALUES (7); GO ALTER TABLE dbo.doc_exz ADD CONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50 FOR column_b; GO CREATE TABLE (T-SQL)
CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT DEFAULT 50);
CONSTRAINT (T-SQL) с именем
CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT CONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50);
Далее
Дополнительные сведения см. в разделе ALTER TABLE (Transact-SQL).
Как сделать значение по умолчанию в sql
Столбцу можно назначить значение по умолчанию. Когда добавляется новая строка и каким-то её столбцам не присваиваются значения, эти столбцы принимают значения по умолчанию. Также команда управления данными может явно указать, что столбцу должно быть присвоено значение по умолчанию, не зная его. (Подробнее команды управления данными описаны в Главе 6.)
Если значение по умолчанию не объявлено явно, им считается значение NULL. Обычно это имеет смысл, так как можно считать, что NULL представляет неизвестные данные.
В определении таблицы значения по умолчанию указываются после типа данных столбца. Например:
CREATE TABLE products ( product_no integer, name text, price numeric DEFAULT 9.99 );
Значение по умолчанию может быть выражением, которое в этом случае вычисляется в момент присваивания значения по умолчанию (а не когда создаётся таблица). Например, столбцу timestamp в качестве значения по умолчания часто присваивается CURRENT_TIMESTAMP , чтобы в момент добавления строки в нём оказалось текущее время. Ещё один распространённый пример — генерация « последовательных номеров » для всех строк. В Postgres Pro это обычно делается примерно так:
CREATE TABLE products ( product_no integer DEFAULT nextval('products_product_no_seq'), . );
здесь функция nextval() выбирает очередное значение из последовательности (см. Раздел 9.16). Это употребление настолько распространено, что для него есть специальная короткая запись:
CREATE TABLE products ( product_no SERIAL, . );
SERIAL обсуждается позже в Подразделе 8.1.4.
| Пред. | Наверх | След. |
| 5.1. Основы таблиц | Начало | 5.3. Ограничения |
SQL-Ex blog
Вставка столбца со значением по умолчанию в таблицу SQL Server
Добавил Sergey Moiseenko on Суббота, 2 октября. 2021
- Ограничение DEFAULT и необходимые разрешения для его создания.
- Добавление ограничения DEFAULT при создании новой таблицы.
- Добавление ограничения DEFAULT в существующую таблицу.
- Модификация и просмотр определения ограничения с помощью скриптов T-SQL и в SSMS.
Что такое ограничение DEFAULT
Ограничение DEFAULT задает значение по умолчанию для столбца.
Когда выполняется оператор INSERT, но не указывается конкретное значение для столбца с созданным ограничением DEFAULT, SQL Server вставляет значение по умолчанию, указанное в определении ограничения DEFAULT.
Чтобы создать ограничение по умолчанию, вам необходимо иметь разрешение на выполнение ALTER TABLE и CREATE TABLE.
Добавление ограничения DEFAULT при создании новой таблицы
Это будет таблица с именем SalesDetails. Когда мы вставляем данные в эту таблицу без указания значения для столбца Sale_Qty, запрос должен вставить нуль. Чтобы добиться этого, я создаю ограничение по умолчанию с именем DF_SalesDetails_SaleQty на столбце Sale_Qty.
USE demodatabase
go
CREATE TABLE salesdetails
(
id INT IDENTITY (1, 1),
product_code VARCHAR(10),
sale_qty INT CONSTRAINT df_salesdetails_saleqty DEFAULT 0
)
Давайте теперь протестируем поведение ограничения, вставив несколько фиктивных записей в таблицу. Выполните следующий запрос:
INSERT INTO salesdetails (product_code)
VALUES ('PROD0001')
Теперь посмотрим, что находится в таблице:
Как можно увидеть, в столбец Sale_Qty был вставлен нуль.
Если при создании таблицы, мы не указываем имя ограничения DEFAULT, SQL Server создает ограничение с уникальным именем, которое генерируется системой.
Создайте таблицу с помощью следующего запроса:
USE demodatabase
go
CREATE TABLE salesdetails
(
id INT IDENTITY (1, 1),
product_code VARCHAR(10),
sale_qty INT DEFAULT 0
)
Выполните следующий скрипт, чтобы увидеть имя ограничения:
SELECT NAME [Constraint name],
parent_object_id [Table Name],
type_desc [Object Type],
definition [Constraint Definition]
FROM sys.default_constraints
SQL Server создал ограничение со сгенерированным системой именем.
Добавление ограничение DEFAULT в существующую таблицу
Чтобы добавить ограничение для существующего столбца таблицы, используется оператор ALTER TABLE ADD CONSTRAINT:
ALTER TABLE [tbl_name]
ADD CONSTRAINT [constraint_name] DEFAULT [default_value] FOR [Column_name]
- tbl_name : задает имя таблицы, в которую вы хотите добавить ограничение по умолчанию.
- constraint_name : задает желаемое имя ограничения.
- column_name : задает имя столбца, для которого вы хотите создать ограничение по умолчанию.
- default_value : задает значение, которое вы хотите использовать при вставке.
Давайте сначала добавим столбец Product_name в SalesDetails:
ALTER TABLE salesdetails
ADD product_name VARCHAR(500)
Вставляем данные в таблицу без указания значения для столбца Product_name. Запрос должен вставить N/A.
Для этого я создам ограничение по умолчанию с именем DF_SalesDetails_ProductName на столбце Product_name. Следующий запрос создает это ограничение:
ALTER TABLE dbo.salesdetails
ADD CONSTRAINT df_salesdetails_productname DEFAULT 'N/A' FOR product_name
Теперь давайте проверим действие ограничения. Вставим запись, не указывая имя товара:
INSERT INTO salesdetails
(product_code,
product_name,
sale_qty)
VALUES ('PROD0002',
'Dell Optiplex 7080',
20)
INSERT INTO salesdetails
(product_code,
sale_qty)
VALUES ('PROD0003',
50)
После вставки записей выполним оператор SELECT, чтобы просмотреть данные:
USE demodatabase
go
SELECT *
FROM salesdetails
go
Как видно на рисунке, значением столбца Product_name для PROD0003 является N/A.
Изменение ограничения DEFAULT
Мы можем изменить определение ограничения по умолчанию: сначала удалить существующее ограничение, а затем создать ограничение с другим определением.
Предположим, что вместо вставки N/A мы хотим вставлять Not Applicable. Сначала мы должны удалить ограничение DF_SalesDetails_ProductName. Выполните следующий запрос:
ALTER TABLE dbo.salesdetails
DROP CONSTRAINT df_salesdetails_productname
После удаления ограничения выполните запрос для создания ограничения:
ALTER TABLE dbo.salesdetails
ADD CONSTRAINT df_salesdetails_productname DEFAULT 'Not Applicable' FOR
product_name
Теперь давайте вставим запись без указания имени товара:
INSERT INTO salesdetails
(product_code,
sale_qty)
VALUES ('PROD0004',
10)
Выполните оператор SELECT для просмотра данных в таблице SalesDetails:
USE demodatabase
go
SELECT *
FROM salesdetails
go
Видно, что значением столбца Product_name является Not Applicable.
Просмотр ограничения DEFAULT
Мы можем увидеть список ограничений DEFAULT с помощью Server Management Studio и выполнив запрос к динамическим административным представлениям.
Откройте SSMS и разверните Databases > DemoDatabase > SalesDetails > Constraint:
Видно, что созданы два ограничения с именами DF_SalesDetails_SaleQty и DF_SalesDetails_ProductName.
Другой способ просмотра ограничений — запрос к sys.default_constraints. Следующий запрос выводит список ограничений по умолчанию и их определения:
SELECT NAME [Constraint name],
Object_name(parent_object_id)[Table Name],
type_desc [Consrtaint Type],
definition [Constraint Definition]
FROM sys.default_constraints
Мы можем использовать хранимую процедуру sp_helpconstraint для просмотра списка ограничений, созданных в таблице:
EXEC Sp_helpconstraint 'SalesDetails'
В столбце constraint_keys выводится определение ограничения по умолчанию.
Удаление ограничения
- Оператор ALTER TABLE DROP CONSTRAINT.
- Оператор DROP DEFAULT.
Alter table [tbl_name] drop constraint [constraint_name]
- tbl_name: задает имя таблицы, которая содержит столбец со значением по умолчанию.
- constraint_name: задает имя ограничения, которое требуется удалить.
ALTER TABLE dbo.salesdetails
DROP CONSTRAINT [DF_SalesDetails_SaleQty]
Проверим, что ограничение было удалено:
SELECT NAME [Constraint name],
Object_name(parent_object_id)[Table Name],
type_desc [Consrtaint Type],
definition [Constraint Definition]
FROM sys.default_constraints
Рассмотрим теперь оператор DROP DEFAULT. Он имеет следующий синтаксис:
DROP DEFAULT [constraint_name]
где constraint_name задает имя ограничения, которое требуется удалить.
Чтобы удалить ограничение с помощью оператора DROP DEFAULT, выполните следующий запрос:
IF EXISTS (SELECT NAME
FROM sys.objects
WHERE NAME = 'DF_SalesDetails_ProductName'
AND type = 'D')
DROP DEFAULT [DF_SalesDetails_ProductName];
Надеюсь, что эта информация и практические примеры поможет в вашей работе.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись