ОБЛАСТЬ ПРИМЕНЕНИЯ: 
SQL Server База данных SQL Azure 
Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
Можно создать ограничение уникальности в SQL Server SQL Server при помощи SQL Server Management Studio SQL Server Management Studio или Transact-SQL Transact-SQL , чтобы гарантировать, что в определенных столбцах, не участвующих в первичном ключе, не будут встречаться повторяющиеся значения. You can create a unique constraint in SQL Server SQL Server by using SQL Server Management Studio SQL Server Management Studio or Transact-SQL Transact-SQL to ensure no duplicate values are entered in specific columns that do not participate in a primary key. Создание ограничения уникальности автоматически приводит к созданию соответствующего уникального индекса. Creating a unique constraint automatically creates a corresponding unique index.
В этом разделе In This Topic
Перед началом работы Before you begin:
Создание ограничения уникальности с использованием: To create a unique constraint, using:
Перед началом Before You Begin
безопасность Security
Permissions Permissions
Требуется разрешение ALTER на таблицу. Requires ALTER permission on the table.
Использование среды SQL Server Management Studio Using SQL Server Management Studio
Создание ограничения уникальности To create a unique constraint
В Обозревателе объектовщелкните правой кнопкой мыши таблицу, в которую необходимо добавить ограничение уникальности, и выберите Конструктор. In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
В меню Конструктор таблиц выберите пункт Индексы и ключи. On the Table Designer menu, click Indexes/Keys.
В диалоговом окне Индексы и ключи нажмите Добавить. In the Indexes/Keys dialog box, click Add.
В сетке в разделе Общиещелкните Тип и выберите Уникальный ключ в раскрывающемся списке справа от свойства. In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.
В меню Файл выберите пункт Сохранитьtable name. On the File menu, click Savetable name.
Использование Transact-SQL Using Transact-SQL
Создание ограничения уникальности To create a unique constraint
В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine Database Engine . In Object Explorer, connect to an instance of Компонент Database Engine Database Engine .
На стандартной панели выберите пункт Создать запрос. On the Standard bar, click New Query.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере создаются таблица TransactionHistoryArchive4 и ограничение уникальности в столбце TransactionID . The example creates the table TransactionHistoryArchive4 and creates a unique constraint on the column TransactionID .
Создание ограничения уникальности в существующей таблице To create a unique constraint on an existing table
В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine Database Engine . In Object Explorer, connect to an instance of Компонент Database Engine Database Engine .
На стандартной панели выберите пункт Создать запрос. On the Standard bar, click New Query.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере создается ограничение уникальности в столбцах PasswordHash и PasswordSalt в таблице Person.Password . The example creates a unique constraint on the columns PasswordHash and PasswordSalt in the table Person.Password .
Создание ограничения уникальности в новой таблице To create a unique constraint in an new table
В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine Database Engine . In Object Explorer, connect to an instance of Компонент Database Engine Database Engine .
На стандартной панели выберите пункт Создать запрос. On the Standard bar, click New Query.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере создается таблица и определяется ограничение уникальности в столбце TransactionID . The example creates a table and defines a unique constraint on the column TransactionID .
Данная статья будет про то, как одна дружная команда веб разработчиков, не имея в своём составе опытного SQL разработчика, добавила Check Constraint в таблицу и прошлась по нескольким простым, но не сразу очевидным граблям. Будут разобраны особенности синтаксиса T-SQL, а также нюансы работы ограничений (СONSTRAINT’ов), не зная которые, можно потратить не мало времени на попытки понять, почему что-то работает не так. Так же будет затронута особенность работы SSDT, а именно как генерируется миграционный скрипт, при необходимости добавить или изменить ограничения (CONSTRAINT’ы).
Дабы читатель поскорей понял, стоит читать статью или нет, я сначала рассмотрю абстрактную задачу, по ходу решения которой будут заданы вопросы «А почему так?». Если вы сразу будете знать ответ, то смело бросайте чтение и переходите к следующей статье.
Разработаем гарем?
«Гарем» — система, которая будет позволять вести учёт людей в «храме любви».
Для простоты разработки примем следующее:
- гости в гареме запрещены и, соответственно, в базе не хранятся, т. е. хранятся только «хозяева» и их жёны
- у жён и их «хозяина» фамилия совпадает
- по фамилии можно уникально идентифицировать каждый гарем, т. е. одна и та же фамилия в разных гаремах встретиться не может.
Для хранения людей создаётся таблица Persons:
В последний момент, приходит озарение, что на уровне схемы базы мы не гарантируем существование только одного мужчины в гареме. Решаем это исправить путём добавления проверочного ограничения (check constraint):
основанного на скалярной пользовательской функции (scalar-valued Function):
«А почему так?» №1.
При попытке вставить абсолютно валидные данные (как женщин, так и мужчин), понимаем, что мы всё поломали. Insert валится со следующей ошибкой:
«А почему так?» №2.
После того как побороли проблему вставки данных, решаем задеплоиться в QA окружение. Создаём миграционный скрипт при помощи SSDT, быстро просматриваем его. Находим какую-то не очень понятную строку (выделена красной рамкой).
Из комментария в инструкции PRINT кажется, что это запуск проверки ограничения на уже существующих строках. Но при создании ограничения мы же указали, что существующие строки проверять не нужно («Check Existing Data On Creation Or Re-Enabling» был установлен в “No”). Поэтому начинаем гуглить и находим «полезный» пост. Прочитав ответ и все комментарии к нему, обретаем глубокую уверенность, что эта инструкция включает проверку при вставке новых строк, а не валидирует существующие, т. е. нам обязательно нужно оставить эту строку, иначе ограничение вообще никогда не будет проверяться.
С гордостью за проделанную работу, отправляем скрипт, ждёмс… Спустя Х часов приходит отчёт, что наш миграционный скрипт успешно провалился. Смотрим отчёт.
Понимаем, что именно подозрительная инструкция провалила миграцию.
«А почему так?» №1 – Объяснение.
Тут всё предельно просто. Мы забыли, что проверка условий CHECK CONSTRAINT’а происходит уже после вставки строки в таблицу и в момент вставки первого мужчины в гарем, правильным условием будет равенство единице, а не нулю. В итоге функция была переписана на много проще.
«А почему так?» №2 – Объяснение.
Тут всё оказалось не столь прозрачно. Сначала пришлось всё-таки разобраться в истинном назначении упавшей инструкции. И, к превеликому нашему удивлению, мы поняли, что она делает именно то, что сказано в комментарии, а не то, что описано в найденном «полезном» посте (разбор синтаксиса будет ниже).
Узнав это, было логично предположить, что при создании миграционного скрипта была выбрана база, в которой на CK_Persons значение «Check Existing Data On Creation Or Re-Enabling» было “Yes”, а не “No”. Но эта теория провалилась. Меняя это значение и генерируя новый скрипт, стало понятно, что SSDT, вообще игнорируют это значение. Начали грешить на наличие бага в SSDT.
Очередной этап поисков навёл нас на следующий пост, из которого мы уже поняли, что это «фича, а не баг».
Согласно дизайна SSDT, при создании скрипта всегда создаётся ограничение, которое включено, т.е. проверяется для всех будущих INSERT/UPDATE. За это отвечает первая инструкция ALTER в нашем миграционном скрипте.
Вторая же инструкция ALTER (выделена красной рамкой) отвечает за валидацию существующих данных и является опциональной. За то будет ли эта инструкция добавлена в миграционный скрип, отвечает специальная опция генерации скрипта:
Включив её, мы для каждого нового миграционного скрипта активируем валидацию существующих данных, т.е. в него будет вставлена опциональная инструкция (второй ALTER). Иначе, инструкция попросту отсутствует и на существующих данных проверка не выполняется. Как это не прискорбно получается, но SSDT генерирует миграционный скрипт по принципу всё или ничего. Можно либо для всех вновь добавляемых ограничений включить проверку на существующих данных, либо для всех её пропустить. Для более тонкой настройки поведения придётся править скрипт вручную.
Ограничения (Constraints) в MS SQL
Как уже говорилось выше, в данной статье осталось разобраться с премудростями синтаксиса создания и обновления проверочных ограничений. Но прежде чем мы приступим, давайте для полноты картины вспомним немного общей информации об ограничениях в MS SQL Server.
Ограничения – механизм, который позволяет задавать набор правил, направленных на поддержание целостности данных. Правила могут быть заданы как на уровне колонки в таблице, так и на уровне всей таблицы.
MS SQL Server поддерживает следующие виды ограничений:
- NULL / NOT NULL ограничение – задаётся на уровне какого-то столбца и определяет, может ли хранится значение NULL в колонке.
- UNIQUE ограничение – позволяет обеспечить уникальность значений в одном или нескольких столбцах.
- PRIMARY KEY ограничение – практически тоже самое, что и UNIQUE ограничение, но в отличие от него, PRIMARY KEY не позволяет хранить NULL.
- CHECK ограничение – позволяет задать некое логическое условие, которое должно быть истинным (TRUE) при вставке или обновлении данных в таблице. Может быть задано как на уровне одного столбца, так и на уровне таблицы.
- FOREIGN KEY ограничение – позволяет обеспечить ссылочную связность двух таблиц. При вставке значения в колонку (или колонки) с FOREIGN KEY ограничением, будет производится проверка на наличие такого же значения в таблице, на которую указывает FOREIGN KEY. Если значения нет, то обновление или вставка строки завершается с ошибкой. Исключением может быть только значение NULL, если на колонке не задано ограничение NOT NULL. Кроме того, ссылаться можно только на колонку с уникальными значениями, т.е. с UNIQUE или PRIMARY KEY ограничением. Так же можно задать поведение, на случай обновления или удаления строки, в «отцовской» таблице:
- NO ACTION – отцовскую таблицу запрещено менять
- CASCADE – подчинённые строки будут обновлены или удалены, в зависимости от выполняемого действием над отцовской таблицей
- SET NULL – значение в подчинённой таблице будет установлено в NULL
- SET DEFAULT — значение в подчинённой таблице будет установлено в значение по умолчанию.
Теперь немного подробней о CHECK CONSTRAINT’ах. Рассмотрим ограничение, которое было упомянуто выше. Ниже представлено окно свойств этого ограничения в Management Studio:
Основными свойствами являются:
- Expression – любое допустимое T-SQL выражение в котором можно ссылаться на значения в проверяемой строке по имени столбцов
- Name – имя, уникально идентифицирующее ограничение в пределах базы данных
- Check Existing Data On Creation Or Re-Enabling – если ограничение создаётся на уже существующей таблице, то это значение “No” позволяет не пропустить валидацию существующих строк; в виду того, что существующую проверку можно временно выключить, то данное свойство так же определяет будет ли проводиться валидация имеющихся строк при включении ограничения.
- Enforce For INSERTs And UPDATEs – включает (Yes) или выключает (No) ограничение
- Enforce For Replication – позволяет пропустить проверку при вставке или обновлении строк агентом репликации
Вся эта информация доступна нам так же из системного представления (view) sys.check_constraints. Оно содержит по одной строке для каждого CHECK CONSTRAINT в базе данных. Мы его иногда используем в миграционных скриптах, когда нужно убедится в существовании или в отсутствии какого-либо ограничения.
Примеры использования sys.check_constraints
Можно получить ответ в более привычном формате, воспользовавшись оператором UNPIVOT:
Особенности работы CHECK CONSTRAINT:
- Срабатывает только при INSERT и UPDATE операциях, при выполнении DELETE условие не проверяется
- Если проверочное условие равно NULL, то считается, что CHECK CONSTRAINT не нарушен
Синтаксис CHECK CONSTRAINT
У проверочного ограничения есть ряд свойств, которые необходимо задать при создании. Некоторые из них можно задать только при создании, а некоторые доступны для изменения, только на уже созданном ограничении. В таблице ниже отображены эти особенности.
| “…ADD CONSTRAINT. ” (создание) | “ALTER…CONSTRAINT…” (изменение) | |
|---|---|---|
| Name | + | — |
| Expression | + | — |
| Check Existing Data On Creation Or Re-Enabling | + | + |
| Enforce For INSERTs And UPDATEs | — | + |
| Enforce For Replication | + | — |
Добавление нового CHECK CONSTRAINT
- В квадратных скобках «[ ]» – указываются опциональные конструкции и могут быть опущены из конечного выражения
- В фигурных скобках «< >» — указывается список возможных конструкций, из которых необходимо выбрать одну
- Вертикальная черта «|» — отделяет элементы в фигурных скобках, среди которых необходимо выбрать единственный элемент
Опциональные секции:
- [ WITH < CHECK | NOCHECK >] – в случае отсутствия применяется значение WITH CHECK
- [ NOT FOR REPLICATION ] – если конструкция указана, то ограничение не проверяется при вставке или обновлении данных в момент репликации; если конструкция пропущена –ограничение проверяется.
Примечание: шаблон приведён для случая создания ограничения на существующей таблице. Также можно создать ограничение в момент создания таблицы, тогда команда будет начинаться со слова CREATE и до слова WITH будет идти описание колонок таблицы.
Примеры команд будут приведены для простейшей таблицы Employees, которая выглядит следующим образом:
Изменение существующего CHECK CONSTRAINT
Для обновления существующего проверочного ограничения используется конструкция ALTER TABLE. Для изменения доступны только следующие свойства:
- Check Existing Data On Creation Or Re-Enabling
- Enforce For INSERTs And UPDATEs
Опциональные секции:
- [ WITH < CHECK | NOCHECK >] – в случае отсутствия применяется значение WITH NOCHECK
- [, …n] – позволяет задать имя более чем одного ограничения, к которым будут применены изменения; использование слова ALL изменения применятся ко всем проверочным ограничениям на таблице
Примечание 1: хоть имя и нельзя переименовать при помощи синтаксиса ALTER TABLE, это всё же возможно сделать, используя системную хранимую процедуру sp_rename.
Примечание 2: при необходимости изменить свойства «Expression» или «Enforce For Replication», необходимо сначала удалить существующее ограничение, а потом заново его создать с нужными значениями этих свойств.
Недокументированное поведение
Есть ряд случаев, когда выполнение команд приводит к неожиданным результатам. Причём я не смог найти объяснение на сайте msdn.
Что бы это увидеть, необходимо рассмотреть все возможные комбинации состояний в сочетании со всеми возможными вариантами команд. Тогда будет видно, что в 5-ти случаях получаемое значение свойства «Check Existing Data» не соответствует ожиданиям.
| Состояние до выполнения команды | T-SQL команда | Состояние после выполнения команды | ||
|---|---|---|---|---|
| Check Existing Data | Enforce For INSERTs And UPDATEs | Check Existing Data | Enforce For INSERTs And UPDATEs | |
| No | No | NOCHECK | No | No |
| No | Yes | NOCHECK | No | No |
| Yes | Yes | NOCHECK | No | No |
| No | No | CHECK | No | Yes |
| No | Yes | CHECK | No | Yes |
| Yes | Yes | CHECK | Yes* | Yes |
| No | No | WITH NOCHECK NOCHECK | No | No |
| No | Yes | WITH NOCHECK NOCHECK | No | No |
| Yes | Yes | WITH NOCHECK NOCHECK | No | No |
| No | No | WITH NOCHECK CHECK | No | Yes |
| No | Yes | WITH NOCHECK CHECK | No | Yes |
| Yes | Yes | WITH NOCHECK CHECK | Yes* | Yes |
| No | No | WITH CHECK NOCHECK | No** | No |
| No | Yes | WITH CHECK NOCHECK | No** | No |
| Yes | Yes | WITH CHECK NOCHECK | No** | No |
| No | No | WITH CHECK CHECK | Yes | Yes |
| No | Yes | WITH CHECK CHECK | Yes | Yes |
| Yes | Yes | WITH CHECK CHECK | Yes | Yes |
(*) Значение свойства «Check Existing Data» может быть переведено из значения «Yes» в значение «No», только если текущее значение свойства «Enforce For INSERTs And UPDATEs» отличается от заданного в команде.
(**) «Check Existing Data» может быть «Yes», только если ограничение включено (Enforce For INSERTs And UPDATEs = “Yes”). Т. е. в команде WITH CHECK NOCHECK часть WITH CHECK будет проигнорирована и «Check Existing Data» не будет установлено в «Yes». Это так же объясняет почему в качестве начальных состояний есть только 3 варианта для каждой команды (а не 4).
Удаление существующего CHECK CONSTRAINT
Команда очень проста и не требует дополнительных объяснений. Ещё шаблон:
Сегодня мы с Вами затронем очень интересную, важную и, наверное, сложную для начинающих тему – это ограничения в 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, так как все, что не касается ограничений в примерах ниже, подразумевается, что Вы уже знаете. Поэтому если Вам что-то не понятно можете найти ответы в следующих материалах:
Пример создания ограничения NOT NULL и DEFAULT
Сначала мы рассмотрим пример создавать ограничения NOT NULL и DEFAULT. Это можно сделать как при создании таблицы, так и после, т.е. добавить ограничение отдельной инструкцией.
В первом случае показано, как создаются ограничения 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 можно создать как во время создания таблицы (причем двумя разными способами), так и после с помощью отдельной инструкции.
Сначала давайте посмотрим, как создается первичный ключ во время создания таблицы.
Первый способ подразумевает определение PRIMARY KEY на уровне столбца, т.е. мы после всех характеристик написали ключевое слово CONSTRAINT, затем название ограничения и тип этого ограничения.
Второй способ заключается в определении ограничения на уровне таблицы, иными словами, после всех столбцов мы пишем ключевое слово CONSTRAINT, имя ограничения, тип и, в данном случае, мы еще указываем какой именно столбец будет у нас выполнять роль первичного ключа (в нашем случае Column1).
Для того чтобы добавить первичный ключ к уже существующей таблице нужно использовать инструкцию ALTER TABLE и команду ADD CONSTRAINT.
В данном случае мы добавили в таблицу TestTable ограничение первичного ключа с названием PK_TestTable, Column1- это столбец, который и будет первичным ключом.
Пример создания ограничения FOREIGN KEY в SQL Server
Ограничения FOREIGN KEY мы также можем определить, как во время создания самой таблицы, так и отдельной инструкцией применительно к уже существующей таблице.
Для примера давайте создадим две таблицы, первая будет содержать данные о категориях товара, а вторая перечень товаров со ссылкой на категорию, т.е. товар должен относиться к какой-нибудь категории.
В данном примере сначала мы создали таблицу, на которую будем ссылаться, т.е. таблицу с категориями, затем мы создали таблицу с товарами и при ее создании определили внешний ключ, т.е. создали ограничение FOREIGN KEY. Для этого мы так же, как и при создании первичного ключа, указали ключевое слово CONSTRAINT, имя ограничения, тип, столбец, который будет ссылаться на ключ в другой таблице. Далее мы написали ключевое слово REFERENCES, указали таблицу, которая содержит ключ, и в скобочках указали название столбца, который и будет ключом, в большинстве случаев данный столбец является первичном ключом, но необязательно.
В примере я также показал, что мы можем назначить некое действие в тех случаях, когда с ключом будет выполнена операция удаления или обновления. Например, категорию товара решили удалить, но это сделать не получится, если на эту запись ссылается записи из таблицы с товарами (к этой категории привязаны товары), иными словами, по умолчанию будет ошибка. Для того чтобы изменить действие по умолчанию мы можем указать в инструкции определения ограничения команды ON DELETE и ON UPDATE, т.е. соответственно действия, которые будут выполнены в случае удаления ключа, и действия, которые будут выполнены, если этот ключ будет обновлен. Возможно указать следующие значения:
- CASCADE – в случае удаления будет выполнено удаление всех ссылающихся записей, т.е. если мы захотим удалить категорию мы также удалим и все товары в этой категории (SQL сервер это сделает автоматически). В случае обновления, все записи будут обновлены;
- SET NULL – присвоить значение NULL, как при удалении, так и при обновлении;
- SET DEFAULT — присвоить значение по умолчанию, как при удалении так и при обновлении;
- NO ACTION – ничего не делать, просто будет ошибка (указывать необязательно, это действие по умолчанию).
Для того чтобы посмотреть, как создается ограничение FOREIGN KEY отдельной инструкцией, давайте удалим таблицу с товарами, затем создадим ее без внешнего ключа, а потом добавим ограничение FOREIGN KEY.
К существующей таблице ограничение FOREIGN KEY добавляется так же, как и другие ограничения, инструкцией ALTER TABLE и командой ADD CONSTRAINT. В этом примере, как видите, я не указал инструкции ON DELETE и ON UPDATE, т.е. действие при удалении или обновлении будет по умолчанию.
Пример создания проверочного ограничения CHECK в MS SQL Server
Ограничения CHECK можно также создать двумя способами, в момент создания самой таблицы и отдельной инструкцией ALTER TABLE.
В первом случае мы создали таблицу и сразу определили в нем проверочное ограничение CK_TestTable6_C1, которое подразумевает, что столбец Column1 не может содержать значение 0. Иными словами, если вдруг Вы или кто-то другой захочет вставить строку со значение Column1 = 0, SQL сервер не разрешит Вам это сделать, он выдаст ошибку.
Во втором случае, мы добавили проверочное ограничение и задействовали при этом два столбца, т.е. мы говорим, что в строках значение столбца Column2 всегда должно быть больше значения, которое в столбце Column1.
Пример создания ограничения UNIQUE
Ограничение уникальности UNIQUE мы можем создать нескольким способами. При создании таблицы на уровне столбца, при создании таблицы на уровне таблицы, и отдельной инструкцией ALTER TABLE ADD CONSTRAINT.
Давайте посмотрим, как это делается.
В итоге мы создали таблицу, в которой три столбца и ко всем этим столбцам мы применили ограничение уникальности UNIQUE разными способами.
Для первого столбца, на уровне самого столбца, для второго отдельной инструкцией, а для третьего на уровне таблицы. Вы можете использовать любой из этих способов, какой Вам удобней. Синтаксис добавления ограничений я думаю понятен.
Надеюсь, материал был Вам полезен, а у меня на этом все, пока!
