Constraint sql add: SQL Ключевое слово ADD CONSTRAINT

CHECK CONSTRAINT в MS SQL — Грабли по которым мы прошлись

Данная статья будет про то, как одна дружная команда веб разработчиков, не имея в своём составе опытного 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’а происходит уже после вставки строки в таблицу и в момент вставки первого мужчины в гарем, правильным условием будет равенство единице, а не нулю. В итоге функция была переписана на много проще.
Вычисляемые колонки (Computed column)

В выражении ограничения можно использовать вычисляемые колонки, но только если они физически сохраняются, т.е. у них свойство IsPersited установлено в Yes. На этапе выполнения проверки, все вычисляемые колонки будут иметь правильные значения и если вы обновите значения, от которых зависит вычисляемое значение, то в выражении 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

Sql

DECLARE @name NVARCHAR(128) = 'CK_Persons'

SELECT	CASE [is_not_trusted] WHEN 1 THEN 'No' ELSE 'Yes' END AS [Check Existing Data],
		CASE [is_disabled] WHEN 1 THEN 'No' ELSE 'Yes' END AS [Enabled],
		CASE [is_not_for_replication] WHEN 1 THEN 'NO' ELSE 'YES' END AS [Enforce For Replication]		
FROM [sys].[check_constraints]
WHERE name = @name

Можно получить ответ в более привычном формате, воспользовавшись оператором UNPIVOT:

Sql

DECLARE @name NVARCHAR(128) = 'CK_Persons'

SELECT [Properties], [Values]
FROM (SELECT CAST([definition] AS VARCHAR(MAX)) AS [Expression],
			 CAST(CASE [is_not_trusted] WHEN 1 THEN 'No' ELSE 'Yes' END AS VARCHAR(MAX)) AS [Check Existing Data On Creation Or Re-Enabling],
			 CAST(CASE [is_disabled] WHEN 1 THEN 'No' ELSE 'Yes' END AS VARCHAR(MAX)) AS [Enforce For INSERTs And UPDATEs],
		     CAST(CASE [is_not_for_replication] WHEN 1 THEN 'NO' ELSE 'YES' END AS VARCHAR(MAX)) AS [Enforce For Replication],
		     CAST([create_date] AS VARCHAR(MAX)) as [Created],
		     CAST([modify_date] AS VARCHAR(MAX)) as [Modified]
	 FROM [sys].[check_constraints]
	 WHERE name = @name) p
UNPIVOT
(
	[Values] FOR [Properties] 
	IN (
		[Expression],
		[Check Existing Data On Creation Or Re-Enabling] ,
		[Enforce For INSERTs And UPDATEs],
		[Enforce For Replication],
		[Created],
		[Modified]
		)
) AS unpvt;

Особенности работы CHECK CONSTRAINT:

• Срабатывает только при INSERT и UPDATE операциях, при выполнении DELETE условие не проверяется
• Если проверочное условие равно NULL, то считается, что CHECK CONSTRAINT не нарушен

Синтаксис CHECK CONSTRAINT

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

Добавление нового CHECK CONSTRAINT

Основы синтаксиса шаблонов T-SQL

• В квадратных скобках «[ ]» – указываются опциональные конструкции и могут быть опущены из конечного выражения
• В фигурных скобках «{ }» — указывается список возможных конструкций, из которых необходимо выбрать одну
• Вертикальная черта «|» — отделяет элементы в фигурных скобках, среди которых необходимо выбрать единственный элемент

Опциональные секции:

1. [ WITH { CHECK | NOCHECK } ] – в случае отсутствия применяется значение WITH CHECK
2. [ NOT FOR REPLICATION ] – если конструкция указана, то ограничение не проверяется при вставке или обновлении данных в момент репликации; если конструкция пропущена –ограничение проверяется.

Примечание: шаблон приведён для случая создания ограничения на существующей таблице. Также можно создать ограничение в момент создания таблицы, тогда команда будет начинаться со слова CREATE и до слова WITH будет идти описание колонок таблицы.

Примеры:

Таблица для примеровПримеры команд будут приведены для простейшей таблицы Employees, которая выглядит следующим образом:

Изменение существующего CHECK CONSTRAINT

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

• Check Existing Data On Creation Or Re-Enabling
• Enforce For INSERTs And UPDATEs

Опциональные секции:

1. [ WITH { CHECK | NOCHECK } ] – в случае отсутствия применяется значение WITH NOCHECK
2. [, …n] – позволяет задать имя более чем одного ограничения, к которым будут применены изменения; использование слова ALL изменения применятся ко всем проверочным ограничениям на таблице

Примечание 1: хоть имя и нельзя переименовать при помощи синтаксиса ALTER TABLE, это всё же возможно сделать, используя системную хранимую процедуру sp_rename.
Примечание 2: при необходимости изменить свойства «Expression» или «Enforce For Replication», необходимо сначала удалить существующее ограничение, а потом заново его создать с нужными значениями этих свойств.

Примеры:

Недокументированное поведение

Есть ряд случаев, когда выполнение команд приводит к неожиданным результатам. Причём я не смог найти объяснение на сайте msdn.
Что бы это увидеть, необходимо рассмотреть все возможные комбинации состояний в сочетании со всеми возможными вариантами команд. Тогда будет видно, что в 5-ти случаях получаемое значение свойства «Check Existing Data» не соответствует ожиданиям.

(*) Значение свойства «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

Команда очень проста и не требует дополнительных объяснений. Ещё шаблон:

Заключение

Искренне надеюсь, что после прочтения данной статьи, вы не пройдётесь по граблям, набившие нам пару неприятных шишек. А так же вы сможете комфортно создавать и поддерживать миграционные скрипты, в которых есть логика по работе с CHECK CONSTRAINT. Удачи!

работа с типами и точностью данных

От автора: ограничения SQL — это правила, применяемые к столбцам данных таблицы. Они используются, чтобы ограничить типы данных, которые могут храниться в таблице. Это обеспечивает точность и надежность данных в базе данных.

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

Ниже приведены некоторые из наиболее часто используемых ограничений, доступных в SQL. Эти ограничения уже рассматривались в главе «Концепции SQL-RDBMS» , но сейчас мы вернемся к ним еще раз.

NOT NULL Constraint — столбец не может иметь значение NULL.

DEFAULT Constraint — задает значение по умолчанию для столбца, если оно не указано.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

UNIQUE Constraint — все значения в столбце должны быть разными.

PRIMARY Key — уникальная идентификация каждой строки/записи в таблице базы данных.

FOREIGN Key — уникально идентифицирует строку/запись в любой другой таблице базы данных.

CHECK Constraint — ограничение CHECK обеспечивает, чтобы все значения в столбце удовлетворяли определенным условиям.

INDEX — используется для быстрого создания данных базы данных.

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

Удаление ограничений

Любое ограничение, которое вы определили, можно удалить с помощью команды ALTER TABLE с параметром DROP CONSTRAINT.
Например, чтобы удалить ограничение первичного ключа в таблице EMPLOYEES, вы можете использовать следующую команду.

ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

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

ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

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

Ограничения целостности

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

Существует множество типов ограничений целостности, которые являются частью общей системы Ссылочной целостности (RI): ограничения по первичному ключу, по внешнему ключу, по уникальности и другие ограничения, которые были перечислены выше.

Источник: //www.tutorialspoint.com/

Редакция: Команда webformyself.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Получить курс сейчас!

Хотите изучить MySQL?

Посмотрите курс по базе данных MySQL!

Смотреть

Проблема добавления внешнего ключа с помощью Alter Table с существующей базой данных MYSQL

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

моим решением было удалить все индексы и внешние ключи, переименовать столбцы id, а затем повторно добавить индексы и внешние ключи.

это отлично работает на mysql 5.1 в windows для базы данных разработки.

Я пошел, чтобы запустить мой сценарий миграции на моем сервере debian, который также использует mysql 5.1, и он дает следующую ошибку:

mysql> ALTER TABLE `company_to_module`
    -> ADD CONSTRAINT `FK82977604FE40A062` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ERROR 1005 (HY000): Can't create table 'jobprep_production.#sql-44a5_76' (errno: 150)

в этой таблице нет значений, которые противоречили бы внешнему ключу, который я пытаюсь добавить. База данных не изменилась. Внешний ключ существовал и раньше… так что данные в порядке. Не будем упоминать, что я взял ту же базу данных, что и на сервере, и она отлично переносится на Windows. Но эти же миграции внешнего ключа не принимают Debian.

столбцы используют один и тот же тип-BIGINT (20)

имена на самом деле существуют в их соответствующих таблицах.

таблицы innodb. У них уже есть внешние ключи в других столбцах, как это. Это не новая база данных.

Я не могу удалить таблицы, потому что это производственная база данных.

таблицы «как есть» в моей базе:

 CREATE TABLE `company_to_module` (
  `company_id` bigint(20) NOT NULL,
  `module_id` bigint(20) NOT NULL,
  KEY `FK8297760442C8F876` (`module_id`),
  KEY `FK82977604FE40A062` (`company_id`) USING BTREE,
  CONSTRAINT `FK8297760442C8F876` FOREIGN KEY (`module_id`) REFERENCES `module` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

и

Create Table: CREATE TABLE `company` (
  `company_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `postal_code` varchar(255) DEFAULT NULL,
  `province_id` bigint(20) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,
  `is_enabled` bit(1) DEFAULT NULL,
  `director_id` bigint(20) DEFAULT NULL,
  `homepage_viewable` bit(1) NOT NULL DEFAULT b'1',
  `courses_created` int(10) NOT NULL DEFAULT '0',
  `header_background` varchar(25) DEFAULT '#172636',
  `display_name` varchar(25) DEFAULT '#ffffff',
  `tab_background` varchar(25) DEFAULT '#284767',
  `tab_text` varchar(25) DEFAULT '#ffffff',
  `hover_tab_background` varchar(25) DEFAULT '#284767',
  `hover_tab_text` varchar(25) DEFAULT '#f2e0bd',
  `selected_tab_background` varchar(25) DEFAULT '#f5f5f5',
  `selected_tab_text` varchar(25) DEFAULT '#172636',
  `hover_table_row_background` varchar(25) DEFAULT '#c0d2e4',
  `link` varchar(25) DEFAULT '#4e6c92',
  PRIMARY KEY (`company_id`),
  KEY `FK61AE555A71DF3E03` (`province_id`),
  KEY `FK61AE555AAC50C977` (`director_id`),
  CONSTRAINT `company_ibfk_1` FOREIGN KEY (`director_id`) REFERENCES `user_account` (`user_account_id`),
  CONSTRAINT `FK61AE555A71DF3E03` FOREIGN KEY (`province_id`) REFERENCES `province` (`province_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8

вот статус innodb:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110415  3:14:34 Error in foreign key constraint of table jobprep_production/#sql-44a5_1bc:
 FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`) ON DELETE RESTRICT ON UPDATE RESTRICT:
Cannot resolve column name close to:
) ON DELETE RESTRICT ON UPDATE RESTRICT

если я попытаюсь удалить индекс из ‘company_to_module’, Я получаю эту ошибку:

#1025 - Error on rename of './jobprep_production/#sql-44a5_23a' to './jobprep_production/company_to_module' (errno: 150) 

вот мои переменные innodb:

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 1048576                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 8388608                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_io_threads          | 4                      |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 1048576                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_open_files               | 300                    |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_stats_on_metadata        | ON                     |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 20                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
+---------------------------------+------------------------+

Я также хочу добавить, что, пока я играл с добавлением внешних ключей, mysql повредил мою базу данных и уничтожил ее. Мне пришлось перезагрузить резервную копию, чтобы попробовать еще раз.

помочь? :/

Alter Table Add Constraint — Как использовать ограничения SQL

Введение

При работе с базами данных вам необходимо управлять данными в базе данных, определяя состояние конкретных данных в заданном столбце. При использовании PostgreSQL в данные накладываются несколько ограничений. Они включают в себя ограничения PRIMARY KEY, ограничение FOREIGN KEY, ограничение CHECK, ограничение UNIQUE и ограничение NOT NULL. Некоторые ограничения, такие как PRIMARY KEY и FOREIGN KEY, полезны при определении и управлении отношениями между данными в различных таблицах в базе данных.В этом руководстве мы сосредоточимся на командах, используемых для добавления ограничений к столбцам таблицы.

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

Синтаксис для добавления ограничений в столбец таблицы следующий:

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

Добавление ограничений первичного ключа

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

Начнем с создания таблицы books со столбцами book_id и book_name, используя следующий оператор:

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

Добавление ограничения FOREIGN KEY

Внешний ключ относится к полю или набору полей в таблице, которые однозначно идентифицируют другую строку в другой таблице.Обычно внешний ключ в одной таблице указывает на первичный ключ в другой таблице.

Внешние ключи добавляются в существующую таблицу с помощью оператора ALTER TABLE. Используется следующий синтаксис:

В приведенном выше синтаксисе child_table — это таблица, которая будет содержать внешний ключ, а родительская таблица должна иметь первичные ключи.C1 и p1 — это столбцы из столбцов child_table и parent_table соответственно. Давайте теперь добавим внешний ключ к существующей таблице в примере:

ДОБАВИТЬ ПРОВЕРКУ ОГРАНИЧЕНИЯ

CHECK constraint — проверка соответствия данных в столбце заданной спецификации.Для добавления проверочного ограничения используется следующий синтаксис:

Пример

Результат выполнения указанной выше команды показан ниже:

ДОБАВИТЬ УНИКАЛЬНЫЕ ограничения

Ограничение

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

Пример

Ниже приведены результаты добавления уникального ограничения в нашу таблицу teacher_details:

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

Константа

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

Пример

Результат будет таким, как показано на изображении ниже:

Заключение

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

Ограничение

SQL DEFAULT — вставьте значение по умолчанию в столбец

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

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

Создание ограничения SQL DEFAULT

Существует два способа создания ограничений DEFAULT для столбцов:

Вы можете назначить ограничение DEFAULT столбцу в операторе CREATE TABLE в виде следующего оператора:

Столбец isbn в таблице books принимает «1-84356-028-3» в качестве значения по умолчанию.Если мы вставим новую строку в таблицу books без указания значения для столбца ISBN , механизм базы данных вставит значение 1-84356-028-3 в столбец isbn . См. Следующий оператор INSERT, который добавляет новую книгу в таблицу books :

Мы можем запросить таблицу books с помощью оператора SELECT, чтобы узнать, какое значение было вставлено в столбец isbn :

В столбце ISBN хранится значение по умолчанию ‘1-84356-028-3 ‘указано в определении таблицы.

Ограничение DEFAULT принимает не только буквальное значение, но также значение, возвращаемое функцией. Например, мы назначаем сегодняшнюю дату в качестве значения по умолчанию для столбца pubdate , используя следующий оператор:

Удаление ограничения SQL DEFAULT

Чтобы удалить существующее ограничение DEFAULT , вы также можете использовать оператор ALTER TABLE следующим образом:

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

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

• Было ли это руководство полезным?
• Да Нет

Внешний ключ SQL

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

Что такое внешний ключ?

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

Создание ограничения внешнего ключа

Чтобы создать ограничение внешнего ключа SQL, родительская таблица должна иметь столбец или столбец первичного ключа с ограничением UNIQUE.

В этом случае таблица Dept является родительской таблицей, имеющей первичный ключ, и на нее будут ссылаться дочерние таблицы, имеющие внешний ключ.

Создать внешний ключ при создании таблицы

Ниже приведен пример сценария T-SQL для создания внешнего ключа SQL при создании таблицы без определения имени ограничения. Если мы не укажем имя ограничения, SQL-сервер создаст ограничение внешнего ключа со случайным именем.

Ниже приведен пример сценария T-SQL для создания внешнего ключа при создании таблицы с определенным именем ограничения.

Создать внешний ключ после создания таблицы

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

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

Создать внешний ключ без проверки существующих данных

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

Если мы создадим ограничение с помощью проверки, это приведет к ошибке, указанной ниже, поскольку уже существующие данные нарушают правило.

Если вы по-прежнему хотите создать ограничение внешнего ключа, игнорируя существующие данные, и проверить правило для дальнейших изменений, используйте «WITH NOCHECK».Созданное вами ограничение помечено как ненадежное.

Создайте внешний ключ с правилами DELETE / UPDATE

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

Мы видим, что данные существуют как в родительской, так и в дочерней таблице.

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

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

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

После выполнения вышеуказанного оператора мы видим, что DeptID обновляется до 3 для записей, имеющих DeptID 2 в дочерней таблице.

Аналогично у нас есть следующие действия.

• SET NULL — устанавливает для значения столбца внешнего ключа SQL значение NULL, когда значение первичного ключа либо удаляется, либо обновляется до нового значения. Если столбец не допускает нулевых значений, обновление / удаление столбца первичного ключа завершается неудачно и вызывает ошибку

• SET DEFAULT — устанавливает значение по умолчанию для столбца внешнего ключа при обновлении или удалении значения первичного ключа.Если ограничение по умолчанию не определено и столбец допускает значение NULL, тогда значение столбца внешнего ключа устанавливается в NULL. Если ограничение по умолчанию не определено и столбец не допускает значения NULL, возникает ошибка ниже, и изменение столбца первичного ключа откатывается.

• НЕТ ДЕЙСТВИЙ — если обновление или удаление столбца первичного ключа не соответствует правилу ограничения, то изменение откатывается.

Чтобы изменить существующий внешний ключ с помощью T-SQL, мы должны сначала удалить ограничение внешнего ключа, а затем воссоздать его с новыми изменениями.

Отключение и включение ограничения внешнего ключа

Отключение ограничения

Чтобы отключить ограничение внешнего ключа SQL, нам нужно использовать инструкцию ниже. Замените имя таблицы и имя ограничения. Когда ограничение внешнего ключа отключено, ограничение помечается как ненадежное.

Включить ограничение

Чтобы включить ограничение обратно, используйте инструкцию ниже.

Включить ограничение с проверкой существующих данных

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

Не для репликации

Когда внешний ключ установлен «НЕ ДЛЯ ПОВТОРЕНИЯ», проверка выполняется только тогда, когда пользователь вставляет, удаляет или обновляет данные.

Проверка не запускается, когда агенты репликации синхронизируют изменения, внесенные пользователем, с другим концом (т.е. подписчиком в случае транзакции и подписчиком и издателем в случае репликации слиянием).

Чтобы создать внешний ключ SQL с «НЕ ДЛЯ ОТМЕНА», используйте приведенный ниже сценарий. По умолчанию ограничение помечено как ненадежное.

Несмотря на то, что мы создаем внешний ключ с «FOR REPLICATION» на издателе, агент моментального снимка создает сценарий как «NOT FOR REPLICATION», и внешний ключ создается не для репликации в подписчике, когда применяется снимок.

Индексирование столбцов внешних ключей

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