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

Первичные ключи — ваш первый шаг к реляционным базам данных. Они ссылаются на внешние ключи в реляционных таблицах. Например, если у вас есть таблица со списком клиентов, столбец «CustomerId» будет уникальным для каждого клиента.

Это может быть ваш столбец первичного ключа. Ваше значение CustomerId будет затем помещено в таблицу “Order”, ​​чтобы связать две таблицы вместе.

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

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

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

Это одна из самых сложных идей для нового разработчика SQL.

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

Например, предположим, что у вас есть таблица клиентов, которая содержит адрес клиента. Поскольку адрес относится к клиенту, он находится в правильном месте. Затем вы создаете таблицу “Order” и добавляете адрес клиента в таблицу “Order”. Этот тип избыточности данных плохо спроектирован.

Таблица Customer и “Order” могут связываться друг с другом, используя связи между первичным и внешним ключами. Что произойдет, если вы забудете обновить адрес клиента в таблице заказов? В результате у вас теперь есть два адреса для клиента, и вы не знаете, какой из них является точным.

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

3. Избегайте NOT IN или IN и используйте вместо этого JOIN

Операторы NOT IN и IN плохо оптимизированы. Они удобны, но обычно их можно заменить простым оператором JOIN. Посмотрите на пример запроса.

SELECT *FROM Customer
WHERE NOT IN (SELECT CustomerId FROM Order)

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

Альтернативный, более эффективный вариант заключается в следующем.

SELECT * FROM Customer c
LEFT JOIN Order o on c.CustomerId = o.CustomerId
WHERE o.CustomerId IS NULL

Оператор LEFT JOIN возвращает тот же набор данных, что и предыдущий оператор, но он гораздо более оптимизирован. Он объединяет две таблицы по первичному и внешнему ключу, что повышает скорость запроса и позволяет избежать предложений NOT IN и IN.

4. Забытые значения NULL и пустые строковые значения

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

 Вы можете использовать значения NULL, если значения отсутствуют, или вы можете использовать фактические литеральные значения, такие как строки нулевой длины или 0 целочисленных значений.

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

Когда вы определите, что вы хотите использовать, убедитесь, что ваши запросы учитывают эти значения. Например, если вы разрешите NULL для фамилии пользователя, вы должны выполнить запрос с использованием фильтра NULL (NOT NULL или IS NULL) в ваших предложениях, чтобы включить или исключить эти записи.

5. Символ звездочки в операторах SELECT

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

SELECT * FROM Customer

Запрос возвращает все значения клиентов, включая любые конфиденциальные данные, которые вы можете хранить в таблице. Что если вы сохраните в таблице пароль клиента или номер социального страхования? Надеемся, что эти значения зашифрованы, но даже наличие хэшированного значения может помочь хакерам. Это также проблема производительности, если у вас есть десятки столбцов в таблице.

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

SELECT CustomerId, FirstName, LastName FROM Customer

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

6. Цикл с слишком многими курсорами

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

 Хотя это может показаться преимуществом, оно может снизить производительность базы данных. Циклы распространены в языках программирования, но они неэффективны в программировании SQL.

 Большинство администраторов баз данных отклоняют процедуры SQL с внедренными курсорами.

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

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

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

7. Несоответствия данных в процедурах назначения на местах

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

Например, вам, вероятно, нужна десятичная точность на 2-3 пункта в столбце, в котором хранится общая стоимость заказа. Если вы назначите этот столбец как целое число, ваша база данных сможет хранить только целые числа без десятичных значений.

 Что происходит с десятичными знаками зависит от вашей платформы базы данных. Он может автоматически обрезать значения или выдать ошибку. Любая альтернатива может создать серьезную ошибку в вашем приложении.

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

Это включает в себя написание запросов — когда вы пишете свои запросы и передаете значения параметров в хранимую процедуру, переменная должна быть объявлена ​​с правильным типом данных. Переменные, которые не представляют тип данных столбца, также будут выдавать ошибки или обрезать данные в процессе.

8. Логические операции OR и AND

При написании ваших запросов легко упустить простой логический порядок. Операторы AND и OR могут значительно изменить набор данных. Вы можете избежать распространенных ошибок SQL, используя круглые скобки или организовав свои операторы для представления логики, которая должна применяться.

Давайте посмотрим на оператор SQL, который смешивает операторы AND и OR.

SELECT CustomerId
FROM Customer
WHERE FirstName = 'AndreyEx' AND LastName = 'Destroyer' OR CustomerId > 0

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

Однако, поскольку мы смешали оператор AND с OR, возвращаются все записи, в которых идентификатор клиента больше нуля. Мы можем преодолеть это логическое препятствие, используя круглые скобки.

Давайте добавим их к приведенному выше утверждению.

SELECT CustomerId
FROM Customer
WHERE (FirstName = 'AndreyEx' OR LastName = 'Destroyer') AND CustomerId > 0

Мы изменили логику для этого утверждения. Теперь первый набор скобок возвращает все записи, в которых имя клиента — AndreyEx или фамилия Destroyer. С помощью этого фильтра мы сообщаем SQL, чтобы он возвращал только те значения, где CustomerId больше нуля.

Эти типы логических утверждений должны быть хорошо проверены перед выпуском их в производство.

9. Подзапросы должны возвращать одну запись

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

Давайте посмотрим на пример.

SELECT CustomerId,
(SELECT OrderId FROM Order o WHERE c.CustomerId = o.CustomerId)
FROM Customer c

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

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

 Вы можете избежать этой проблемы, используя оператор «Top 1».

Давайте изменим запрос на следующий.

SELECT CustomerId,
(SELECT Top 1 OrderId FROM Order o WHERE c.CustomerId = o.CustomerId ORDER BY OrderDate)
FROM Customer c

В приведенном выше запросе мы извлекаем только одну запись и упорядочиваем записи по дате. Этот запрос получает первый заказ, размещенный клиентом.

10. JOIN к индексам

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

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

 Однако внешний ключ также должен иметь индекс.

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

Заключение

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

Чистый SQL код

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

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

Если запрос состоит из одного select из одной таблицы, то его можно записать как попало. Следующий запрос особо оформлять и не нужно, он легко читается:

select * from person

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

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

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

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

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

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

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

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

select personid, teamid, firstname, lastname, positionid from person

Вот тут уже все не так все гладко читается. Если команды SQL написать большими буквами, что очень часто делают, то текст уже становиться чуть лучше:

SELECT personid, teamid, firstname, lastname, positionid FROM person

Это еще не все, можно разделить на строки, каждую секцию написать в своей строке:

SELECT personid, teamid, firstname, lastname, positionid
FROM person

Тут даже если написать все маленькими буквами все тоже будет в принципе читаемым:

select personid, teamid, firstname, lastname, positionid
from person

Да, это читаемо, но все же на мой вкус все писать большими буквами лучше.

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

Если писать колонки в отдельной строке то можно снизить вероятность конфликтов:

SELECT personid,
teamid,
firstname,
lastname,
positionid
FROM person

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

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

SELECT personid,
teamid,
— firstname,
lastname,
positionid
FROM person

Теперь я отключил колонку firstname и это сделать было очень просто. В оформленном таким образом запросе проще сортировать колонки, если забыть про последнюю.

SELECT personid,
firstname,
lastname,
teamid,
positionid
FROM person

Недостаток – перемещать удобно любую колонку, кроме последней. С конфликтами тоже была проблема с последней колонкой, причем чтобы добавить новую колонку программисты будут не только добавлять еще одну строку, но еще и трогать последнюю строку с колонками, туда придется добавить запятую после positioned. Эта запятая может стать проблемой, потому что она как раз усложняет слияние.

Запятую лучше писать вначале:

SELECT personid
, firstname
, lastname
, teamid
, positionid
FROM person

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

Да, для огромных запросов, которые возвращают более 20 колонок, такой запрос начинает занимать очень много места в файле и приходиться очень много скролить в коде. Скролить тоже плохо. И вот мы встаем перед выбором – что лучше? Зависит от ситуации и личных предпочтений, я же все же рекомендовал бы писать имена колонки каждую в своей строке.

Усложняем жизнь, добавляем объединение таблиц:

SELECT personid
, firstname
, lastname
, teamid
, p.positionid
, psn.Name
FROM person p
JOIN position psn on p.positionid = psn.positionid

Некоторые явно предпочитают писать INNER JOIN, чтобы указать, что перед нами именно INNER объединение. Я за счет лени не пишу лишние пять букв, а сокращаю все до JOIN, потому что привык, что именно это поведение по умолчанию. Тут у меня проблем с чтением нет.

Чуть другое дело, если что-то касается – где писать дополнительные фильтры. Например, следующие два запроса будут абсолютно идентичны:

SELECT personid
, firstname
, lastname
, teamid
, p.positionid
, psn.Name
FROM person p
JOIN position psn on p.positionid = psn.positionid and psn.Name = 'Coach'

И

SELECT personid
, firstname
, lastname
, teamid
, p.positionid
, psn.Name
FROM person p
JOIN position psn on p.positionid = psn.positionid
WHERE psn.Name = 'Coach'

И даже этот запрос

SELECT personid
, firstname
, lastname
, teamid
, p.positionid
, psn.Name
FROM person p
JOIN position psn on 1=1
WHERE p.positionid = psn.positionid
AND psn.Name = 'Coach'

Вот реально все равно, где вы напишите проверку на Name = 'Coach', потому что в случае с INNER JOIN она не повлияет на результат и с точки зрения производительности я не видел разницы, потому что оптимизаторы обычно не смотрят, где вы пишите фильтры. Но проверка Name = 'Coach' логически не относится к объединению двух таблиц и поэтому не должна быть в JOIN, она должна быть в секции WHERE, потому что просто логически принадлежит этой секции.

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

Если результат не совсем верный и отображаются только тренера, то скорей всего нужно смотреть на фильтры, а значит это секция WHERE.

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

Посмотрим на секцию WHERE в последнем запросе:

WHERE p.positionid = psn.positionid
AND psn.Name = 'Coach'

Обратите внимание, что каждый фильтр находиться в отдельной строке. Каждый AND должен быть отдельно. В одну строку можно помещать две проверки, только если они объединены с помощью OR, это удобно, чтобы не забыть поставить скобки:

Ограничения SQL: как создать, примеры

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

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

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

Что такое ограничения таблицы SQL

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

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

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

Добавление ограничений SQL

Создать ограничения SQL можно, используя инструкции PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK и NOT NULL.

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

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

CREATE TABLE Countries (Country VARCHAR(46) NOT NULL,Capital VARCHAR(46)

)

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

INSERT INTO Countries VALUES (null, 'Madrid')

Результатом будет эта ошибка:

Column 'Country' cannot be null

А вот такая запись ошибки не вызовет, потому что оставлять пустым столбец с названиями столиц (Capital) мы не запрещали:

INSERT INTO Countries VALUES ('Spain', null)

Ограничение NOT NULL может быть полезно для столбцов с контактными данными, когда нам нужно обязать пользователя, например, ввести свою электронную почту или номер телефона. Поэтому такие обязательные поля нередко используют ограничение NOT NULL, чтобы гарантировать, что пользователь введет определенное значение:

CREATE TABLE Subscribers (SubscriberName VARCHAR(46) NOT NULL,SubscriberContact VARCHAR(46) NOT NULL,

)

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

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

Uniqueзначит «уникальный», и это название полностью отражает суть ограничения. Таким образом, ограничение UNIQUE гарантирует, что никакие два значения в определяемом столбце не будут одинаковыми. Давайте посмотрим на таблицу, в которой используется UNIQUE:

CREATE TABLE Workers1 (WorkerName VARCHAR(46) NOT NULL,WorkerDate DATE,WorkerContact INTEGER UNIQUE

)

Мы создали таблицу работников, в которую будем добавлять имя работника (поле не может быть пустым, так как мы установили для него уже знакомое ограничение NOT NULL), дату приема на работу (в формате даты, на что указывает тип данных DATE) и номер телефона. При этом номер телефона должен быть уникальным, на что и указывает ограничение UNIQUE. Давайте вставим в нашу таблицу следующие данные:

INSERT INTO Workers1 VALUES ('Vasya Pupkin', DATE '2018-05-10', 89009000000)

Теперь при попытке добавления строки с таким же номером телефона:

INSERT INTO Workers1 VALUES ('Petya Pupkin', DATE '2020-06-11', 89009000000)

Программа выдаст ошибку:

Duplicate entry '89009000000' for key 'uniqueconstraint.WorkerContact'

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

Таким образом, если таблица содержит столбцы, в которых хранятся номера паспорта и СНИЛС, эти столбцы должны использовать ограничение UNIQUE.

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

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

Checkв переводе с английского значит «проверять», и ограничение CHECKслужит для проверки значений по определенному условию. Рассмотрим следующий пример:

CREATE TABLE Customers1 (CustomerName1 VARCHAR(46),CustomerName2 VARCHAR(46),CustomerEmail VARCHAR(56),CustomerAge INTEGER CHECK (CustomerAge>17)

)

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

INSERT INTO Customers1 VALUES ('Vasya', 'Pupkin', 'vasya_pupkin@anysite.com', 17)

Вот что нам выдаст система:

Check constraint 'checkconstraint_chk_1' is violated

Инструкцию CHECK можно использовать для реализации пользовательских ограничений.

Так, если в таблице должны храниться только данные взрослых, мы могли бы использовать ограничение CHECK для столбца «Возраст покупателя» (CustomerAge>17, как в примере выше).

Другой пример: если в таблице должны храниться данные только граждан России, мы могли бы использовать CHECK: например, для нового столбца CustomerCountry: CHECK (CustomerCountry='Russia').

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

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

Добавим, что PRIMARY KEY в таблице может относиться только к одному столбцу (и это понятно, так как это идентификатор).

Соответственно, каждое значение PRIMARY KEY обязательно должно быть уникальным, при этом нулевые значения в столбце, определенном с помощью PRIMARY KEY, не допускаются. Чтобы было понятнее, о чём речь, рассмотрим следующий пример:

CREATE TABLE Workers2 (id INTEGER PRIMARY KEY,WorkerName1 VARCHAR(46),WorkerName2 VARCHAR(46),WorkerAge INTEGER CHECK (WorkerAge>17)

)

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

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

Ограничение FOREIGN KEY (внешний ключ) создает ссылку на PRIMARY KEY из другой таблицы.

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

Чтобы было понятнее, что делает этот ключ, давайте посмотрим на пример ограничения FOREIGN KEY, связанного с PRIMARY KEY из уже созданной выше таблицы:

CREATE TABLE WorkersTaxes (WorkerTax INTEGER,Worker_id INTEGER,FOREIGN KEY (Worker_id) REFERENCES Workers2(id)

)

Итак, нам понадобилось создать таблицу для расчета налогов работников.

И, чтобы связать эту таблицу (WorkersTaxes) с таблицей работников (Workers2), мы использовали ссылку FOREIGN KEY, которая идентифицирует работников по PRIMARY KEYиз таблицы Workers2.

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

Другие ограничения

Осталось добавить, что к ограничениям SQL Standard также иногда относят DEFAULT, однако DEFAULT не ограничивает тип вводимых данных, поэтому технически не может быть отнесен к ограничениям.

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

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

CREATE TABLE Customers2 (CustomerName1 VARCHAR(46) NOT NULL,CustomerName2 VARCHAR(46) NOT NULL,CustomerAge INTEGER DEFAULT 18,

)

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

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

Блоки try-catch для программистов на T-SQL_часть 3

• Другие части статьи:
• 1
• 2
• 3
• вперед »

Мы уже обсудили довольно много аспектов перехвата ошибок, но не коснулись аспекта, возможно, главного: а как вся эта «музыка» работает с такой вещью как транзакция? Ведь наши «потенциально опасные» инструкции исполняются обычно в рамках как-раз таки транзакций! Если ответить на поставленный вопрос коротко, то это будет «все работает». Но сказать что «все работает и нет никаких нюансов» было бы непростительной самонадеянностью, как раз к нюансам взаимодействия блоков перехвата и транзакций давайте переходить.

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

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

Как у нас вообще могут «сойтись» в одной точке кода и транзакции, и блоки перехвата и обработки? Очевидно — что-то во что-то вкладывается, как же еще? Не менее очевидно что способов вложения всего два. Или «TRY в транзакции»:

BEGIN TRAN     BEGIN TRY         …         …         COMMIT TRAN     END TRY     BEGIN CATCH         ROLLBACK TRAN         …         …     END CATCH

Или «транзакция в TRY»:

BEGIN TRY     BEGIN TRAN     …     …     COMMIT TRAN END TRY BEGIN CATCH     ROLLBACK TRAN     …     … END CATCH

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

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

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

Хорошо, вот эти две вещи соединились — какое главное правило такого «слияния»? Очень простое: если в блоке TRY у вас есть «ваша» открытая транзакция (открыли ли вы ее методом 1, или методом 2 — не важно), то совершенно все (без исключения!) пути исполнения вашего кода как в блоке TRY, так и в блоке CATCH обязаны вести к завершению транзакции хоть фиксацией («коммит»), хоть откатом («ролбэк»). Худшее что вы можете сделать — покинуть блок TRY/CATCH оставив транзакцию в «подвешенном» состоянии. Ну а каков план реализации этого «очень простого правила»? А вот тут и начинаются обещанные нюансы…

План «генеральный», в общем-то, уже показан в двух отрывках кода выше.

Если блок TRY пройден почти до конца, то перед самой меткой END TRY мы транзакцию фиксируем — ведь мы не испытали никаких проблем при ее исполнении, верно? Конечно, никто не может нам запретить из анализа некоторой информации транзакцию в той же точке откатить, но чаще — фиксируем.

Ну а если мы «свалились» в блок CATCH, то сразу же за меткой BEGIN CATCH (или, по крайней мере, недалеко от нее) мы транзакцию откатываем. А есть ли тут у нас свобода выбора безусловно присутствующая в блоке предыдущем? Можем ли мы находясь в блоке CATCH транзакцию все же зафиксировать? It, как говорится, depends. Давайте к коду:

12345678910111213141516171819202122232425262728

USE tempdb GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 create table dbo.T1 (id int not null) go SET XACT_ABORT OFF BEGIN TRY     BEGIN TRANSACTION     INSERT T1 VALUES (1)     INSERT T1 VALUES (NULL)     INSERT T1 VALUES (2)     COMMIT TRANSACTION END TRY BEGIN CATCH     IF XACT_STATE() = 0         PRINT 'Нет открытых транзакций!'     ELSE IF XACT_STATE() = -1         BEGIN             PRINT 'Транзакция открыта и находится в нефиксируемом состоянии, начинаю откат…'             ROLLBACK TRANSACTION         END     ELSE IF XACT_STATE() = 1         BEGIN             PRINT 'Транзакция открыта и находится в фиксируемом состоянии, начинаю фиксацию…'             COMMIT TRANSACTION         END END CATCH SELECT * FROM T1

Результаты:

и

Транзакция открыта и находится в фиксируемом состоянии, начинаю фиксацию…

Стало быть, один факт установлен доподлинно — есть варианты! И даже в блоке CATCH. Но, во-первых, обратите внимание, что из двух легальных значений колонки ID зафиксировалось (да и было вставлено, на самом деле) лишь первое.

Ведь уходя в CATCH мы в TRY не возвращаемся, не забыли? А, во-вторых, обращают на себя внимание две новых синтаксических конструкции: XACT_ABORT до начала транзакции и XACT_STATE в блоке CATCH. «Разбор полетов» начнем с последней.

Функция XACT_STATE сообщает нам о состоянии текущей транзакции «внутри» которой мы находимся в момент вызова этой функции. XACT_STATE возвращает нам целое число и причем варианта всего три:

• 0 — никакой транзакции вообще нет, не о чем и беспокоиться в плане ее корректного завершения;
• 1 — активная транзакция есть и находится в состоянии допускающим как ее откат, так и ее фиксацию, выбор за нами;
• -1 — тоже активная транзакция есть, но она перешла в специальное «нефиксируемое» состояние. Единственно возможная операция с такой транзакцией — полный и безусловный ее откат. Который, тем не менее, не выполняется движком сервера в автоматическом режиме. И указание инструкции ROLLBACK остается прерогативой и обязанностью (причем одной из главнейших) нашего T-SQL программиста.

Ну с первым значением вопросов нет, отсутствует транзакция — так и нам спокойнее. А вот с двумя вторыми значениями? Отчего бывает так, а бывает и эдак? А это зависит от серьезности той ошибки что привела нас в блок CATCH. Если ошибка «жесткая» мы получаем -1, если «мягкая» — +1.

Кстати говоря, если та же самая транзакция будет исполняться вне блока TRY/CATCH то «жесткая» ошибка приведет к полной отмене всей транзакции, со всеми инструкциями ее составляющими.

А ошибка «мягкая» приведет к выкидыванию из состава транзакции лишь «плохой» инструкции, остальные инструкции той же транзакции будут продолжены. Скажем исполнение вот такого кода:

BEGIN TRANSACTION INSERT T1 VALUES (1) INSERT T1 VALUES (NULL) INSERT T1 VALUES (2) COMMIT TRANSACTION

приведет к вставке двух строк и плюс к предупреждению

Cannot insert the value NULL into column 'id', table 'tempdb.dbo.T1'; column does not allow nulls. INSERT fails.

И это все потому, что вставка нелегального значения — ошибка «мягкая». Измените строчку ее вызывающую, т.е.

на

1	ALTER TABLE T1 DROP CONSTRAINT NonExist

и ни одна строка вставлена не будет, подавляющее число ошибок команд группы DDL — «жесткие». И, как вы правильно понимаете, если мы исправленный вариант нашей транзакции вновь «обернем» в TRY/CATCH, то сообщение будет

Транзакция открыта и находится в нефиксируемом состоянии, начинаю откат…

и уж конечно ни одна из вставляемых строк в таблице T1 обнаружена не будет. Так что разница между 1 и -1 возвращаемыми нам функцией XACT_STATE сводится к «жесткости» ошибки.

Хорошо, а роль второй инструкции, XACT_ABORT какова? А вот какова. Если значение этой опции (как и все прочие «SET-опции» она устанавливается для текущей сессии соединения с сервером) OFF — то «мягкие» ошибки будут «мягкими».

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

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

Вы помните, что в предыдущий раз у нас одна строка вставилась в таблицу T1 и эта вставка была успешно зафиксирована. А что теперь?

Транзакция открыта и находится в нефиксируемом состоянии, начинаю откат…

и полное отсутствие записей в таблице T1.

Ну а какое значение опции XACT_ABORT «правильное»? Под каким лучше работать? А вот это — хороший вопрос если вы планируете начать новую «священную войну» на техническом форуме.

Скажем так: если бы «хорошесть» того или другого варианта была доказана неопровержимо и на 120%, то команде разработчиков, надо думать, не составило бы труда прописать это «победившее» значение в код движка, а опцию XACT_ABORT просто убрать с глаз долой. Ан нет — не прописывают и не убирают.

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

Во-первых, при работе с распределенными запросами и распределенными транзакциями данное значение должно быть выставлено для XACT_ABORT без всяких дискуссий, просто потому что так предписывает BOL. Но распределенные запросы — случай частный и специфичный, так что аргумент получается хоть и неопровержимый, но уж очень «узконаправленный», не масштабный. Однако есть и более весомое во-вторых. Вот код:

123456789101112131415161718

USE tempdb GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 create table dbo.T1 (id int not null) go SET XACT_ABORT OFF BEGIN TRY     BEGIN TRANSACTION     INSERT T1 VALUES (1)     INSERT T1 VALUES (2)     —тут много работы…     WAITFOR DELAY '00:00:10'     COMMIT TRANSACTION END TRY BEGIN CATCH     PRINT 'Готов к откату транзакции…'     ROLLBACK TRANSACTION END CATCH

Запустите показанный код в студии и пока он «вращается» в заданной 10-ти секундной задержке прервите его выполнение нажав кнопку Cancel Executing Query на панели студии либо выбрав одноименный пункт в меню Query.

Если бы наш клиент предполагал выход из запроса по слишком долгому времени потраченному на его исполнение (query timeout) то можно было бы и выждать данный отрезок времени не «снося» запрос принудительно — все дальнейшие замечания не потеря ли бы своей актуальности. Так вот, отменив запрос мы видим что блок CATCH не отработал.

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

1	SELECT * FROM sys.dm_tran_active_transactions WHERE transaction_type=1

И будет она, транзакция наша, так и «висеть», пока клиент не удосужится сделать ей принудительный откат. Но «висит-то» она не просто «висит»: она сохраняет все свои блокировки, на ее поддержку тратятся ресурсы и т.д.

Да, если клиент «отвалится» (закроет соединение) то требуемый откат совершит сам движок, без участия клиента. Однако тут есть большая «засада», помимо очевидной, что клиент еще должен «отвалиться» и не факт что это случится спустя микросекунду после отмены им «долгоиграющего» запроса.

«Засада» заключается в том, что большинство современных платформ создания приложений пользователя (а энтузиасты ваявшие софт на чистом ассемблере давно перевелись) используют парадигму «пула подключений» (connection pool).

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

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

Откатив из первой вкладки редактора проблемную транзакцию (ROLLBACK TRANSACTION), изменим в последнем скрипте значение опции на пропагандируемый автором блога ON. Вновь запустим тот же запрос и вновь прервем. Да, CATCH вновь не отработал — мы ж ему не дали этого сделать.

Но и транзакции нет! В чем можно убедиться вновь запросив динамическое представление sys.dm_tran_active_transactions, как это было в первом эксперименте. Нету! Закрыто принудительно движком без всякого нашего вмешательства.

Что, по мнению многих SQL-специалистов и автора блога так же, много, много лучше чем в варианте со значением OFF. Разумеется, вы можете сказать, что раз клиент открыл транзакцию, а потом отказался от запроса, то… «соображать же надо!». Надо, кто ж спорит.

И правильно написанный клиент не только сделает Cancel, но и еще ROLLBACK своей же транзакции, причем сначала второе и лишь затем первое. Да вот кабы все те клиенты да были правильно написаны…

Можно привести и третий аргумент в поддержку опции ON. Как кажется автору (и, могу вас уверить, не только ему) поведение «ошибка — откат всей транзакции» является интуитивно ожидаемым, а поведение «ошибка — транзакция продолжается» является контр-интуитивным. Однако тут у каждого своя интуиция, не буду спорить.

Как бы то ни было, опыт автора по написанию кучи строк T-SQL кода однозначно говорит о том, что опция ON обеспечивает нас желаемым и ожидаемым поведением транзакций и блоков TRY/CATCH в 99% случаев. Я ни разу не встречал ситуации когда транзакцию прерванную или превысившую свой тайм-аут не нужно было бы откатывать.

А если это точно нужно — чего тянуть? Оставшиеся 1% когда оправдано применение опции OFF это случаи поиска и «отлова» конкретной ошибки в моем T-SQL коде и причем я еще хочу продолжить его исполнение после нахождения той ошибки за которой «охочусь».

Повторю, что число таких случаев, с точки зрения автора, исчезающе мало, хоть и не равно абсолютному нулю.

Итак, теперь у читателей данного материала достаточно фактов что бы сделать свой выбор правильного (не абсолютно правильного, а правильного для конкретно их системы, за разработку/поддержку которой они отвечают) значения для этой «скользкой» опции — XACT_ABORT. Помимо всего изложенного выше при осуществлении такого выбора пусть читатели еще учтут:

• значением по умолчанию для данной опции, как уже отмечалось, является OFF. Так что утвердить безусловный, полный и необсуждаемый ON в масштабах предприятия/решения/команды разработки не так-то просто, людям свойственно стараться «плыть по течению». Потребуются контроль, дисциплина и самодисциплина;
• значение ON дает микроскопический, совершенно не ощутимый на практике плюс для производительности в силу того, что при таком значении движок мгновенно принимает решении об откате проблемной транзакции. Однако, повторю, плюс этот будет измеряться такими долями микросекунд, что автор отмечает его здесь исключительно для полноты изложения, а не в качестве аргумента для принятия решения;
• ни та, ни другая опция не имеют отношения к вопросу «произойдет ли сваливание в блок CATCH». Иными словами, если данная ошибка отправляет поток исполнения в блок CATCH — он отправится туда и в случае ON, и в случае OFF. Потому что это определяется не опцией XACT_ABORT, а… чем? Правильно — номером серьезности возникшей ошибки. XACT_ABORT отвечает на вопрос будут ли у нас варианты с нашей транзакцией в том же блоке, или нас ожидает ROLLBACK, только ROLLBACK и ничего кроме него.

С практической точки зрения, автор рекомендует своим читателям начинать любой T-SQL код со строки SET XACT_ABORT ON. Если этот код будет кодом создания новой хранимой процедуры, то просто возведите степень настойчивости данной рекомендации в квадрат.

Если эта новая хранимая процедура планирует работу с явными транзакциями — в куб. А с еще более практической точки зрения заведите себе шаблон (template) для оформления своих хранимых процедур со всеми «обвесами» по умолчанию.

В качестве «точки старта» можете воспользоваться шаблоном автора:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253