Обслуживание сайта: SQL-команды. Ms sql команды

Основные команды SQL

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

Основные команды SQL

http://qaat.ru/wp-content/uploads/2017/04/sql-150x150.jpg

Не редко в тестировании необходимо проверить какие-либо изменения в базе данных. Для этого необходимо знать основные команды, чтобы сделать выборку по данным или добавить свои данные. Далее я разместил основные команды и правила составления запросов в SQL базах данных. Если вы хотите иметь под рукой основные команды с баз данных, отличных от представленных в статье, […]
Facebook
Twitter
ВКонтакте
Одноклассники
Mail.ru
Google+
Livejournal

28.04.2017 Administrator Пособия Нет комментариев sql, база данных, выборка, запрос

qaat.ru

НОУ ИНТУИТ | Лекция | Основные компоненты Microsoft SQL Server 2008. Создание файла данных. Управление базами данных при помощи команд языка T-SQL

Аннотация: Здесь представлены основные компоненты Microsoft SQL Server 2008. Описано создание файла данных и управление им при помощи команд языка T-SQL.

Цели:

Изучить систему основных компонентов Microsoft SQL Server 2008
Понять процесс создания файла данных
Освоить управление базами данных при помощи команд языка T-SQL

Основные компоненты Microsoft SQL Server 2008

Все компоненты Microsoft SQL Server 2008 запускаются из меню "Пуск \ Программы \ Microsoft SQL Server 2008. В Microsoft SQL Server 2008 входят следующие компоненты:

Deployment Wizard - мастер по выводу информации хранимой на сервере;
SQL Server Installation Center - центр установки SQL Server 2008;
Reporting Services Configuration Manager - менеджер службы настройки отчётов;
SQL Server Configuration Manager - менеджер настройки сервера;
SQL Server Error and Usage Reporting - служба протоколирования работы сервера и служба отчётов об ошибках;
Microsoft Samples Overview - ссылка на сайт корпорации Microsoft, где можно просмотреть примеры работы с сервером;
SQL Server Books Online - полная справочная система по Microsoft SQL Server 2008. Она содержит справки, как по программированию, так и по администрированию сервера;

SQL Server Tutorials - учебники по работе с сервером;
Data Profile Viewer - просмотр профилей по работе с данными;
Execute Package Utility - инструменты по сжатию данных;
Database Engine Tuning Advisor - мастер настройки ядра базы данных;
SQL Server Profiler - настройка профилей по работе с данными;
Import and Export Data - импорт и экспорт данных;
SQL Server Business Intelligence Development Studio - интегрированная среда разработки Business Intelligence Development Studio;
SQL Server Management Studio - графическая оболочка для управления сервером и разработки баз данных.

Создание файла данных

Новую БД можно создать, используя стандартные команды языка T-SQL. Для создания новой БД необходимо сделать активную БД "Master". Это можно сделать либо выбором ее из выпадающего списка БД на панели инструментов, либо набором команды USE Master на вкладке нового запроса.

Замечание: Все команды языка T-SQL набираются на вкладке нового запроса (SQLQuery). Для того чтобы создать новый запрос на панели инструментов необходимо нажать кнопку

Для выполнения команд языка T-SQL на панели инструментов необходимо нажать кнопку или на вкладке нового запроса набрать команду GO.

Замечание: В Microsoft SQL Server БД состоит из двух частей:

Файл данных - файл, имеющий расширение mdf и где находятся все таблицы и запросы;
Файл журнала транзакций - файл, имеющий расширение ldf, содержит журнал, где фиксируются все действия с БД. Данный файл предназначен для восстановления БД в случае её выхода из строя.

Для создания нового файла данных используется команда CREATE DATABASE, которая имеет следующий синтаксис:

CREATE DATABASE <Имя БД> ON (Name=<Логическое имя>, FileName=<Имя файла> [Size=<Нач.размер>,] [Maxsize=<Макс.размер>,] [FileGrowth=<Шаг>]) [LOG ON (Name=<Логическое имя>, FileName=<Имя файла> [Size=<Нач.размер>,] [Maxsize=<Макс.размер >,] [FileGrowth=<Шаг>])

Здесь:

Имя БД - имя создаваемой БД
Логическое имя - определяет логическое имя файла данных БД, по которому происходит обращение к файлу данных.
Имя файла - определяет полный путь к файлу данных.
Нач.размер - начальный размер файла данных в Мб.
Макс.размер - максимальный размер файла данных в Мб.
Шаг - шаг увеличения файла данных, либо в Мб либо в %.

Параметры в разделе LOG ON аналогичны параметрам в разделе CREATE DATABASE. Однако они определяют параметры журнала транзакций.

Пример: Создать БД "Students", расположенную в файле "D:\Students.mdf" и имеющую начальный размер файла данных 1мб., максимальный размер файла данных 100мб. и шаг увеличения файла данных равный 1мб. Файл журнала транзакций данной БД имеет имя "StudentsLog" и расположен в файле "D:\Students.ldf". Данный файл имеет начальный размер равный 1мб., максимальный размер равный 100мб. и шаг увеличения равный 1мб.

CREATE DATABASE Students ON (Name = Students, FileName = 'D:\Students.mdf', Size = 1Mb, Maxsize = 100Mb, FileGrowth= 1Mb) LOG ON (Name = StudentsLog, FileName = 'D:\Students.ldf', Size = 1Mb, Maxsize = 100Mb, FileGrowth = 1Mb)

Управление базами данных при помощи команд языка T-SQL

В языке запросов T-SQL с БД возможны следующие действия:

Отображение сведений о БД: EXEC sp_helpdb <Имя БД> ;
Изменение параметров БД: EXEC sp_dboption <Имя БД>, <Параметр>, <Значение> ;
Добавления новых файлов, удаление файлов и переименования файлов, входящих в БД:ALTER DATABASE <Имя БД> ADD FILE (<Параметры>)| REMOVE FILE <Логическое имя файла>| MODIFY FILE (<Параметры>) где, раздел ADD FILE - добавляет файл, REMOVE FILE - удаляет, а раздел MODIFY FILE - изменяет параметры файла;

Сжатие всей БД: DBCC SHRINKDATABASE <Имя БД> ;
Сжатие конкретного файла БД: DBCC SHRINKFILE <Логическое имя файла> ;
Переименование БД: EXEC SP_RENAMEDB <Имя БД>,<Новое имя БД> ;
Удаление БД: DROP DATABASE <Имя БД>.

Замечание: Вышеперечисленные команды используют следующие параметры:

<Имя БД> - имя БД с которой производится действие;
<Параметр> - изменяемый параметр;
<Значение> - новое значение изменяемого параметра;

<Параметры> - параметры файла БД, аналогичные параметрам, используемым в команде CREATE DATABASE ;
<Логическое имя файла> - логическое имя файла, входящего в БД;
<Новое имя БД> - новое имя БД.

На этом мы заканчиваем рассмотрение файлов данных. Дополнительную информацию можно найти в "Создание файла данных и журнала транзакций" .

www.intuit.ru

SQL команды - Команды языка определения данных DDL, Команды языка управления данными DCL, Команды языка управления транзакциями TCL, Команды языка манипулирования данными DML

Выделяют следующие группы команд SQL:

Команды языка определения данных

Команды языка определения данных DDL (Data Definition Language, язык определения данных) — это подмножество SQL, используемое для определения и модификации различных структур данных.К данной группе относятся команды предназначенные для создания, изменения и удаления различных объектов базы данных. Команды CREATE (создание), ALTER (модификация) и DROP (удаление) имеют большинство типов объектов баз данных (таблиц, представлений, процедур, триггеров, табличных областей, пользователей и др.). Т.е. существует множество команд DDL, например, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE USER, CREATE ROLE и т.д.

Некоторым кажется, что применение DDL является прерогативой администраторов базы данных, а операторы DML должны писать разработчики, но эти два языка не так-то просто разделить. Сложно организовать аффективный доступ к данным и их обработку, не понимая, какие структуры доступны и как они связаны. Также сложно проектировать соответствующие структуры, не зная, как они будут обрабатываться.

Команды языка управления данными

С помощью команд языка управления данными ( DCL (Data Control Language) ) можно управлять доступом пользователей к базе данных. Операторы управления данными включают в себя применяемые для предоставления и отмены полномочий команды GRANT и REVOKE, а также команду SET ROLE, которая разрешает или запрещает роли для текущего сеанса.

Команды языка управления транзакциями

Команды языка управления транзакциями ( TCL (Тгаnsасtiоn Соntrol Language) ) команды позволяют определить исход транзакции.Команды управления транзакциями управляют изменениями в базе данных, которые осуществляются командами манипулирования данными.Транзакция (или логическая единица работы) – неделимая с точки зрения воздействия на базу данных последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации) такая, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует.COMMIT — заканчивает («подтверждает») текущую транзакцию и делает постоянными (сохраняет в базе данных) изменения, осуществленные этой транзакцией. Также стирает точки сохранения этой транзакции и освобождает ее блокировки. Можно также использовать эту команду для того, чтобы вручную подтвердить сомнительную распределенную транзакцию.ROLLBACK — выполняет откат транзакции, т.е. отменяет все изменения, сделанные в текущей транзакции. Можно также использовать эту команду для того, чтобы вручную отменить работу, проделанную сомнительной распределенной транзакцией.Понятие транзакции имеет непосредственную связь с понятием целостности базы данных. Очень часто база данных может обладать такими ограничениями целостности, которые просто невозможно не нарушить, выполняя только один оператор изменения БД. Например, невозможно принять сотрудника в отдел, название и код которого отсутствует в базе данных.В системах с развитыми средствами ограничения и контроля целостности каждая транзакция начинается при целостном состоянии базы данных и должна оставить это состояние целостными после своего завершения. Несоблюдение этого условия приводит к тому, что вместо фиксации результатов транзакции происходит ее откат (т.е. вместо оператора COMMIT выполняется оператор ROLLBACK), и база данных остается в таком состоянии, в котором находилась к моменту начала транзакции, т.е. в целостном состоянии.В связи со свойством сохранения целостности БД транзакции являются подходящими единицами изолированности пользователей, т.е., если с каждым сеансом работы с базой данных ассоциируется транзакция, то каждый пользователь начинает работу с согласованным состоянием базы данных, т.е. с таким состоянием, в котором база данных могла бы находиться, даже если бы пользователь работал с ней в одиночку.

Команды языка манипулирования данными

Команды языка манипулирования данными DML (Data Manipulation Language) позволяют пользователю перемещать данные в базу данных и из нее:

INSERT — осуществляет вставку строк в таблицу.
DELETE — осуществляет удаление строк из таблицы.
UPDATE — осуществляет модификацию данных в таблице.
SELECT — осуществляет выборку данных из таблиц по запросу.

Каждый, кто работает с SQL в среде Oracle, должен вооружиться книгами: справочником по языку SQL, таким как «Oracle SQL: The Essential Reference? (O’Reilly), руководством по оптимизации производительности, например «Oracle SQL Tuning Pocket Reference» (O’Reilly).

sql-language.ru

49. Структура команды sql

Каждая команда SQL начинается с действия – ключевого слова или группы слов, описывающих выполняемую операцию. Например, INSERT (добавить), DELETE (удалить), COMMIT (завершить), CREATE TABLE (создать таблицу).

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

После действия может следовать одно или несколько предложений. Предложение описывает данные, с которыми работает команда, или содержит уточняющую информацию о действии, выполняемом командой. Каждое предложение начинается с ключевого слова, такого как, например, WHERE (где), FROM (откуда), INTO (куда), HAVING (имеющий). Многие предложения содержат имена таблиц и полей БД; некоторые – константы и выражения.

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

Пример команды SQL:

50. Типы данных и выражения sql

Типы данных

Символьный тип данных содержащий буквы, цифры, специальные символы

CHAR или CHAR (n) – символьные строки фиксированные данные

VARCHAR (n) – символьные строки

Целые числа

INTЕGER или INT – целое для решения которого отводится, как байта

SMALLINT – короткое целое (2 байта)

FLOAT – число плавающих точек

DECIMAL (p) – аналогично FLOAT с числовым значение цифр р

DECIMAL (p, n) – аналогично предыдущим, р – общее количество десятичных чисел

MONEY (p, n) – аналогично типу DECIMAL (p, n)

Дата и время

DATE - дата

TIME - время

INTERVAL – временный интервал

DATETIME – момент время

BINARY

BYTE

BLOB – хранить данные любого объема в двоичном коде

SERIAL – тип данных на основе INTEGER позволяющий сформировать уникальные значения

Арифметические выражения

+, -, *, %, /, ^,

Логические операции

AND – логическое умножение

OR – лог сложение

NOT –лог отриц

Текстовые операции

& - слияние слов

Пример выражения

Kol*Price

(Kol*Price)/8200

AVG

Язык SQL оперирует терминами: таблица, строка, столбец или колонка.

Полное имя таблицы: имя _ владельца.имя_таблицы

Полное имя столбца: имя _ владельца.имя_столбца

Основной яз SQL составляет операции, условно разбитые на несколько групп.

Категории операторов SQL:

Date Definition Language (DDC)
Date Manipulation Language (DML)
Date Control Language (DCL)
Transaction Control Language (TCL)
Cursor Control Language (CCL)

51.Возможности языка sql по: определению данных, внесению изменений в базу данных, извлечению данных из базы.

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

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

Категория извлечение данных состоит из одной команды SELECT, являющейся основной, наиболее часто используемой командой языка SQL. Эта команда применяется при формировании всех запросов выбора.

Операторы DDL (Data Definition Language) – операторы определения объектов БД.

Create Schema – создать схему;
Create Table – создать таблицу;
Drop Schema – удалить схему;
Alter Table – изменить таблицу;
Drop Table – удалить таблицу;
Create View – создать представление;
Drop View – удалить представление.

Операторы DML (Data Manipulation Language) – операторы манипулирования данными.

Select – отобрать строки из табл.;
Insert – добавление строки в табл.;
Update – изменить строки в табл.;
Delete – удалить строки в табл.

studfiles.net

Обслуживание сайта: SQL-команды | Microsoft Docs

12/19/2014
Время чтения: 2 мин

В этой статье

Назначение: System Center Configuration Manager 2007, System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3, System Center Configuration Manager 2007 SP1, System Center Configuration Manager 2007 SP2

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

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

Важно!

Configuration Manager 2007 не проверяет синтаксис команд SQL. Перед планированием пользовательской задачи обслуживания убедитесь в допустимости команды SQL, протестировав ее в среде SQL Server Management Studio.

ИмяПозволяет указать имя выбранной команды. Команда передается на сервер SQL Server в том виде, в котором она была введена (проверка синтаксиса не выполняется). Например, чтобы запустить хранимую процедуру, которая отображает данные об использовании пространства базы данных, введите sp_spaceused.

Включить команду SQLВключает выбранную команду. Чтобы запланировать команду, ее необходимо включить.

Записать в журнал состояниеПозволяет указать имя и путь к файлу журнала, в который будут записываться данные о состоянии выполнения команды. Путь может быть любым допустимым сетевым путем (например, \\<myserver>\logs\command1.log). Журналы можно просматривать в любом простом текстовом редакторе, таком как Блокнот.

Примечание

Если путь к журналу не указан, данные о состоянии выполнения команды записываться не будут.

РасписаниеПозволяет указать расписание запуска выбранной задачи.

Запуск послеПозволяет указать самое раннее время начала выполнения выбранной задачи.

Самое позднее время запускаПозволяет указать самое позднее время начала выполнения выбранной задачи.

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

См. также

Задачи

Создание пользовательской задачи обслуживания

Основные понятия

Настраиваемые задачи обслуживания

Дополнительные сведения см. на странице Configuration Manager 2007 Information and Support [Информация и поддержка Configuration Manager 2007].Для обращений в группу разработчиков документации используйте адрес электронной почты [email protected].

technet.microsoft.com

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

SqlDECLARE @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:SqlDECLARE @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

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

Name	+	-
Expression	+	-
Check Existing Data On Creation Or Re-Enabling	+	+
Enforce For INSERTs And UPDATEs	-	+
Enforce For Replication	+	-

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

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

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

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

[ 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

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

Заключение

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

habr.com