Ограничения в Microsoft SQL Server — что это такое и как их создать?

Сегодня мы с Вами затронем очень интересную, важную и, наверное, сложную для начинающих тему – это ограничения в Microsoft SQL Server. После прочтения статьи Вы узнаете, для чего нужны ограничения, какие типы ограничений бывают в SQL Server, а также научитесь создавать эти ограничения.

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

Что такое ограничения в Microsoft SQL Server?

Ограничения – это специальные объекты в Microsoft SQL Server, с помощью которых можно задать правила допустимости определенных значений в столбцах с целью обеспечения автоматической целостности базы данных. Другими словами, ограничения формируют некое условие на те данные, которые будут вводиться в таблицу и храниться в ней. Например, какие-то данные должны быть уникальными, какие-то данные из разных таблиц должны быть неразрывно связаны по общему ключу, а какие-то данные вообще хранить не стоит, т.е. в каком-нибудь столбце не должно быть определенных значений.

Ограничения, как я уже сказал, очень важные объекты в SQL сервере, так как в процессе планирования структуры таблицы Вы всегда должны продумывать допустимые значения, которые могут храниться в том или ином столбце. Вручную контролировать процесс добавления и хранения данных в корректном виде просто невозможно. Поэтому, SQL сервер предоставляет специальный механизм, с помощью которого мы можем установить жесткие правила на ввод и хранение данных, иными словами, автоматизировать процесс контроля корректности данных. Если Вы захотите обойтись без использования ограничений при проектировании и реализации базы данных, то в скором времени у Вас будет не база данных, а просто набор непонятной и несогласованной информации.

Типы ограничений в SQL Server

В Microsoft SQL Server реализовано несколько типов ограничений, каждое из которых предназначено для выполнения какой-то конкретной задачи, и сейчас мы с Вами рассмотрим эти типы.

Ограничение NOT NULL

Это ограничение, с помощью которого мы можем запретить или наоборот разрешить хранение в столбце значений NULL, т.е. неопределенных значений. Таким образом, мы можем сказать, что если у нас запрещены значения NULL в столбце, то этот столбец является обязательным к заполнению, а если у нас разрешены значения NULL, то столбец можно и не заполнять, т.е. данное ограничение поможет нам контролировать внесение и хранение обязательных характеристик той или иной сущности. И, конечно же, Вы должны знать, что наличие значений NULL в базе данных — это не очень хорошо, поэтому данное ограничение помогает исключить такие значения.

С этим ограничением Вы, наверное, уже сталкивалась, и неоднократно работали, так как при создании таблицы, или добавления нового столбца, мы практически всегда указываем возможность принятия столбцом значений NULL, для этого мы пишем NULL или NOT NULL в определении таблицы.

Ограничение PRIMARY KEY

PRIMARY KEY – ограничение первичного ключа. Первичный ключ – это столбец или комбинация столбцов, значения которых гарантируют уникальность каждой строки, что дает нам возможность идентифицировать каждую строку в таблице по данному ключу.

PRIMARY KEY должен быть практически в каждой таблице, и он должен быть у нее один. Обычно первичный ключ создают для столбца, который выполняет роль счетчика (IDENTITY), и он не может содержать значения NULL. Создав ограничение PRIMARY KEY, Вы можете не беспокоиться о том, что в Вашей таблице вдруг окажется две записи с одинаковым идентификатором.

Ограничение FOREIGN KEY

FOREIGN KEY – это ограничение внешнего ключа. Ограничение FOREIGN KEY предназначено для установления связи между данными в таблицах. Иными словами, если в таблице есть ключ (столбец, обычно идентификатор), который есть и в другой таблице, то эти таблицы должны быть связаны с помощью ограничения FOREIGN KEY. Таким образом, с помощью данного ограничения мы выстраиваем связь между таблицами в базе данных.

Ограничение FOREIGN KEY обеспечивает ссылочную целостность, оно позволяет исключить ситуации, когда, например, у Вас в одной таблице есть записи, которые ссылаются на отсутствующие записи в другой таблицы, т.е. этих записей нет, в итоге получаются некорректные данные.

Ограничение UNIQUE

UNIQUE – это ограничение, которое обеспечивает уникальность значений в столбце или комбинации столбцов. UNIQUE позволяет исключить повторяющиеся значения в столбце. В отличие от PRIMARY KEY, для таблицы можно задать несколько ограничений UNIQUE, и столбец, для которого определено данное ограничение, может содержать значение NULL (но, как Вы понимаете, такое значение может быть только одно в этом столбце). В случае если столбцы в таблице были определены без ограничения UNIQUE при создании таблицы, то для того чтобы добавить этого ограничения, в соответствующем столбце не должно быть повторяющихся значений.

Ограничение CHECK

CHECK – это проверочное ограничение. Данное ограничение проверяет данные, на предмет выполнения определенных условий, при вводе в таблицу. Иными словами, если Вам требуется, чтобы в столбце хранились только значения, которые отвечают определённым требованиям, то как раз с помощью ограничение CHECK Вы можете автоматизировать процесс контроля за вводом данных. Например, по бизнес требованию, цена товара не должна быть отрицательной, для этого в таблице для столбца, который хранит цену товара, мы можем определить проверочное ограничение CHECK, которое будет проверять все значения, вносимые в данный столбец. Таким образом, мы на уровне сервера задаем четкие правила допустимых значений определенных столбцов.

К одному столбцу в таблице мы можем применять несколько проверочных ограничений. Создать проверочное ограничение UNIQUE можно с любым логическим выражением, которое возвращает значение TRUE или FALSE.

Ограничение DEFAULT

DEFAULT – это значение по умолчанию. Мы уже говорили о том, что значение NULL — это не очень хорошо, поэтому еще одним способом избавления от данного значения, является возможность задать для столбца значение по умолчанию, которое будет сохранено, если при вводе данных мы не указали никакого значения. Например, если в столбец с ценой товара не указать цену, когда мы будет добавлять новый товар, то SQL сервер автоматически добавит значение по умолчанию, которое мы укажем при определении этого ограничения, к примеру, 0.

Примеры создания ограничений в Microsoft SQL Server

Сейчас давайте рассмотрим примеры создания и добавления ограничений на языке T-SQL. Все ограничения также можно создавать и с помощью графической среды SQL Server Management Studio.

Примечание! Все примеры ниже выполнены в Microsoft SQL Server 2016 Express. Также рекомендую Вам ознакомиться с основами языка T-SQL, так как все, что не касается ограничений в примерах ниже, подразумевается, что Вы уже знаете. Поэтому если Вам что-то не понятно можете найти ответы в следующих материалах:

• Видеокурсы по T-SQL;
• Справочник Transact-SQL;
• SQL код – самоучитель по SQL для начинающих программистов.

Пример создания ограничения NOT NULL и DEFAULT

Сначала мы рассмотрим пример создавать ограничения NOT NULL и DEFAULT. Это можно сделать как при создании таблицы, так и после, т.е. добавить ограничение отдельной инструкцией.

   
   --1. Создаем таблицу с ограничениями NOT NULL и DEFAULT
   CREATE TABLE TestTable(
           [Column1] [INT] NOT NULL,
           [Column2] [INT] NULL DEFAULT (0),
           [Column3] [INT] NULL CONSTRAINT DF_C3 DEFAULT (0)
   )
   GO
   --2. Добавляем к столбцу Column2 ограничение NOT NULL
   ALTER TABLE TestTable ALTER COLUMN [Column2] [INT] NOT NULL 
   GO
   --3. Добавляем к столбцу Column1 ограничение DEFAULT
   ALTER TABLE TestTable ADD CONSTRAINT DF_C1 DEFAULT (0) FOR Column1

В первом случае показано, как создаются ограничения NOT NULL и DEFAULT во время создания таблицы.

Для первого столбца мы просто указали NOT NULL, что говорит о том, что данный столбец не может содержать значения NULL.

Для второго столбца мы задали значение по умолчанию 0, с помощью ключевого слова DEFAULT, это сокращённая запись добавления данного ограничения.

Для третьего столбца мы также задали значение по умолчанию 0, но при этом использовали полное определение ограничения с применением ключевого слова CONSTRAINT (DF_C3 — это имя ограничения).

Во второй инструкции показано, как добавлять ограничение NOT NULL к существующей таблице. Как видите, это делается с помощью инструкции ALTER TABLE и команды ALTER COLUMN. Изначально столбец Column2 у нас мог принимать значение NULL, после выполнения этой инструкции не может. Однако стоит помнить о том, что, если в столбце уже будут значения NULL, инструкция не выполнится.

В третьей инструкции мы добавили к столбцу Column1 ограничение DEFAULT, для этого мы также использовали инструкцию ALTER COLUMN, а для добавления ограничения команду ADD CONSTRAINT, после которой мы написали имя ограничения (DF_C1), тип и с помощью ключевого слова FOR указали столбец, для которого мы хотим создать ограничение.

Пример создания ограничения PRIMARY KEY в Microsoft SQL Server

Ограничения первичного ключа PRIMARY KEY можно создать как во время создания таблицы (причем двумя разными способами), так и после с помощью отдельной инструкции.

Сначала давайте посмотрим, как создается первичный ключ во время создания таблицы.

   
   --Первый способ определения первичного ключа
   CREATE TABLE TestTable2(
        [Column1] [INT] IDENTITY(1,1) NOT NULL CONSTRAINT PK_Column1_T2 PRIMARY KEY,
        [Column2] [VARCHAR](100) NOT NULL
   )

   GO

   --Второй способ определения первичного ключа
   CREATE TABLE TestTable3(
        [Column1] [INT] IDENTITY(1,1) NOT NULL,
        [Column2] [VARCHAR](100) NOT NULL,
        CONSTRAINT PK_Column1_T3 PRIMARY KEY (Column1)
  )

Первый способ подразумевает определение PRIMARY KEY на уровне столбца, т.е. мы после всех характеристик написали ключевое слово CONSTRAINT, затем название ограничения и тип этого ограничения.

Второй способ заключается в определении ограничения на уровне таблицы, иными словами, после всех столбцов мы пишем ключевое слово CONSTRAINT, имя ограничения, тип и, в данном случае, мы еще указываем какой именно столбец будет у нас выполнять роль первичного ключа (в нашем случае Column1).

Для того чтобы добавить первичный ключ к уже существующей таблице нужно использовать инструкцию ALTER TABLE и команду ADD CONSTRAINT.

   
   ALTER TABLE TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY (Column1)

В данном случае мы добавили в таблицу TestTable ограничение первичного ключа с названием PK_TestTable, Column1- это столбец, который и будет первичным ключом.

Пример создания ограничения FOREIGN KEY в SQL Server

Ограничения FOREIGN KEY мы также можем определить, как во время создания самой таблицы, так и отдельной инструкцией применительно к уже существующей таблице.

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

   
   --Создаем таблицу, на которую будем ссылаться
   CREATE TABLE TestTable4(
        [CategoryId] [INT] IDENTITY(1,1) NOT NULL,
        [CategoryName] [VARCHAR](100) NOT NULL,
    CONSTRAINT PK_TestTable4 PRIMARY KEY (CategoryId)
   )

   --Создаем таблицу и ограничение FOREIGN KEY
   CREATE TABLE TestTable5(
        [ProductId] [INT] IDENTITY(1,1) NOT NULL,
        [CategoryId] [INT] NOT NULL,
        [ProductName] [VARCHAR](100) NOT NULL,
        [Price] [MONEY] NULL,
    CONSTRAINT PK_TestTable5 PRIMARY KEY (ProductId),
    CONSTRAINT FK_TestTable5 FOREIGN KEY (CategoryId) REFERENCES TestTable4 (CategoryId)
        ON DELETE CASCADE --Не обязательно. Также возможно: NO ACTION | SET NULL | SET DEFAULT
        ON UPDATE CASCADE --Не обязательно. Также возможно: NO ACTION | SET NULL | SET DEFAULT
   )

В данном примере сначала мы создали таблицу, на которую будем ссылаться, т.е. таблицу с категориями, затем мы создали таблицу с товарами и при ее создании определили внешний ключ, т.е. создали ограничение FOREIGN KEY. Для этого мы так же, как и при создании первичного ключа, указали ключевое слово CONSTRAINT, имя ограничения, тип, столбец, который будет ссылаться на ключ в другой таблице. Далее мы написали ключевое слово REFERENCES, указали таблицу, которая содержит ключ, и в скобочках указали название столбца, который и будет ключом, в большинстве случаев данный столбец является первичном ключом, но необязательно.

В примере я также показал, что мы можем назначить некое действие в тех случаях, когда с ключом будет выполнена операция удаления или обновления. Например, категорию товара решили удалить, но это сделать не получится, если на эту запись ссылается записи из таблицы с товарами (к этой категории привязаны товары), иными словами, по умолчанию будет ошибка. Для того чтобы изменить действие по умолчанию мы можем указать в инструкции определения ограничения команды ON DELETE и ON UPDATE, т.е. соответственно действия, которые будут выполнены в случае удаления ключа, и действия, которые будут выполнены, если этот ключ будет обновлен. Возможно указать следующие значения:

• CASCADE – в случае удаления будет выполнено удаление всех ссылающихся записей, т.е. если мы захотим удалить категорию мы также удалим и все товары в этой категории (SQL сервер это сделает автоматически). В случае обновления, все записи будут обновлены;
• SET NULL – присвоить значение NULL, как при удалении, так и при обновлении;
• SET DEFAULT — присвоить значение по умолчанию, как при удалении так и при обновлении;
• NO ACTION – ничего не делать, просто будет ошибка (указывать необязательно, это действие по умолчанию).

Для того чтобы посмотреть, как создается ограничение FOREIGN KEY отдельной инструкцией, давайте удалим таблицу с товарами, затем создадим ее без внешнего ключа, а потом добавим ограничение FOREIGN KEY.

   
   --Удаляем таблицу
   DROP TABLE TestTable5

   --Создаем таблицу без ограничения FOREIGN KEY
   CREATE TABLE TestTable5(
        [ProductId] [INT] IDENTITY(1,1) NOT NULL,
        [CategoryId] [INT] NOT NULL,
        [ProductName] [VARCHAR](100) NOT NULL,
        [Price] [MONEY] NULL,
    CONSTRAINT PK_TestTable5 PRIMARY KEY (ProductId)
   )

   --Добавляем ограничение FOREIGN KEY
   ALTER TABLE TestTable5 ADD CONSTRAINT FK_TestTable5 FOREIGN KEY (CategoryId) 
        REFERENCES TestTable4 (CategoryId);

К существующей таблице ограничение FOREIGN KEY добавляется так же, как и другие ограничения, инструкцией ALTER TABLE и командой ADD CONSTRAINT. В этом примере, как видите, я не указал инструкции ON DELETE и ON UPDATE, т.е. действие при удалении или обновлении будет по умолчанию.

Пример создания проверочного ограничения CHECK в MS SQL Server

Ограничения CHECK можно также создать двумя способами, в момент создания самой таблицы и отдельной инструкцией ALTER TABLE.

   
   --Создаем проверочное ограничение CHECK в определении таблицы
   CREATE TABLE TestTable6(
        [Column1] [INT] NOT NULL,
        [Column2] [INT] NOT NULL,
        CONSTRAINT CK_TestTable6_C1 CHECK (Column1 <> 0)
   )
   --Добавляем ограничение CHECK к существующей таблице
   ALTER TABLE TestTable6 ADD CONSTRAINT CK_TestTable6_C2 CHECK (Column2 > Column1);

В первом случае мы создали таблицу и сразу определили в нем проверочное ограничение CK_TestTable6_C1, которое подразумевает, что столбец Column1 не может содержать значение 0. Иными словами, если вдруг Вы или кто-то другой захочет вставить строку со значение Column1 = 0, SQL сервер не разрешит Вам это сделать, он выдаст ошибку.

Во втором случае, мы добавили проверочное ограничение и задействовали при этом два столбца, т.е. мы говорим, что в строках значение столбца Column2 всегда должно быть больше значения, которое в столбце Column1.

Пример создания ограничения UNIQUE

Ограничение уникальности UNIQUE мы можем создать нескольким способами. При создании таблицы на уровне столбца, при создании таблицы на уровне таблицы, и отдельной инструкцией ALTER TABLE ADD CONSTRAINT.

Давайте посмотрим, как это делается.

   
   --Создаем таблицу и ограничение UNIQUE
   CREATE TABLE TestTable7(
        [Column1] [INT] NOT NULL CONSTRAINT U_TestTable7_C1 UNIQUE,
        [Column2] [INT] NOT NULL,
        [Column3] [INT] NOT NULL,
        CONSTRAINT U_TestTable7_C2 UNIQUE (Column2)
   )
   --Добавляем к существующей таблице ограничение UNIQUE
   ALTER TABLE TestTable7 ADD CONSTRAINT U_TestTable7_C3 UNIQUE (Column3);

В итоге мы создали таблицу, в которой три столбца и ко всем этим столбцам мы применили ограничение уникальности UNIQUE разными способами.

Для первого столбца, на уровне самого столбца, для второго отдельной инструкцией, а для третьего на уровне таблицы. Вы можете использовать любой из этих способов, какой Вам удобней. Синтаксис добавления ограничений я думаю понятен.

Надеюсь, материал был Вам полезен, а у меня на этом все, пока!