SQL-Ex blog

Что такое ограничение 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
: задает значение, которое вы хотите использовать при вставке.

Когда значение column_name не задается в операторе INSERT, вставляется значение, указанное в параметре 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 DROP CONSTRAINT. Она имеет следующий синтаксис:

Alter table [tbl_name] drop constraint [constraint_name]

Здесь

• tbl_name: задает имя таблицы, которая содержит столбец со значением по умолчанию.


• constraint_name: задает имя ограничения, которое требуется удалить.

Предположим, мы хотим удалить ограничение DF_SalesDetails_SaleQty из таблицы SalesDetails. Выполните запрос:

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]; 

Надеюсь, что эта информация и практические примеры поможет в вашей работе.