Базовый курс SQL. Другие возможности SQL

[к оглавлению]

Ограничения в SQL

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

Ограничения связей между таблицами

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

Если бы СУБД не предоставляла возможности установить ключи, нам бы пришлось самостоятельно следить за сохранением целостности данных, выполняя запросы SELECT. Это бы сильно усложнило работу:

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

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

Различия в СУБД

Как всегда, каждая СУБД имеет свои особенности и может поддерживать разный набор ограничений. Чтобы убедиться в корректности вашего кода, обратитесь к документации СУБД.

Ограничения по уникальности

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

Какие отличия есть между простым ограничением и первичным ключом:

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

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

Ограничения по значению столбца

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

CREATE TABLE Students
(
  student_id      int       NOT NULL ,
  student_name    char(50)  NOT NULL ,
  student_surname char(50)  NOT NULL ,
  student_gender    char(1)  NOT NULL ,
  student_age     int       NOT NULL  CHECK (student_age > 16),
  student_country char(50)  NOT NULL ,
  student_email   char(255) ,
  group_id        int       NOT NULL
);

Здесь мы видим, что минимальный возраст студентов ограничивают 16 годами. Это может быть обусловлено внутренними правилами вуза или объективной реальностью. Так или иначе, если ввести в поле student_age число меньше 16, СУБД выдаст ошибку, не позволив сохранить неподходящие данные.

Чаще всего используют следующие ограничения:

• максимальное и минимальное значение чисел или дат
• диапазон значений, например, если дата должна попадпть в определённый срок
• конкретные варианты разрешённых значений, например, обозначения пола: male / female

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

ALTER TABLE Students
ADD CONSTRAINT CHECK (student_gender LIKE '[mf]');

Собственный тип данных

Если какое-либо ограничение применяется в вашей БД много раз, можно сохранить его в отдельный тип данных. Обычно это бывает стандартный тип, но с дополнительным условием: например, gender, обозначающий обыкновенную строку, но допускающий ввод только двух букв: m и f. Это позволит не повторять всю конструкцию CHECK в нескольких таблицах, а указывать gender без дополнительных пояснений.

Индексы в SQL

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

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

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

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

Создать индекс можно с помощью команды CREATE INDEX:

CREATE INDEX teachers_surname_idx
ON Teachers (teacher_surname);

Здесь teachers_surname_idx - это имя индекса, оно должно быть уникальным в базе данных. После ключевого слова ON указывается таблица и, в скобочках, столбцы для сортировки (их может быть и несколько).

Обновление индексов

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

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

• индексы увеличивают производительность извлечения данных, но уменьшают - добавления, изменения и удаления, т.к. при таких операциях СУБД вынуждена динамически обновлять все индексы таблицы
• сохраняемые индексы занимают место на диске
• есть столбцы, которые не имеет смысла индексировать (например, student_gender таблицы Students, в отличие от teacher_surname, имеет всего 2 значения, и значит, эффективность поиска возрастёт минимально)
• а вот если вы часто выполняете поиск по какому-то столбцу, то это наилучшее место для использования индекса
• можно добавить в индекс и несколько столбцов таблицы (но это целесообразно только если извлечение данных производится по тем же столбцам)

Триггеры в SQL

Триггеры - это те же хранимые процедуры, но выполняемые автоматически при наступлении конкретных событий (например, команды UPDATE, DELETE или INSERT в определённой таблице). Это значит, что триггер, связанный с командой INSERT и таблицей Groups, запустится при создании новой строки в Groups.

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

Цели создания триггеров:

• обеспечение единообразности данных (например, при создании факультетов, проверять, что название начинается с большой буквы)
• внесение изменений в связанную таблицу (например, при удалении учебной группы, для всех её студентов необходимо выбрать новую грппу)
• валидация данных (например, проверка при отчислении студента, что у него действительно больше двух неудовлетворительных оценок за последнюю сессию)
• рассчёт вычисляемых полей или обновление временных меток

В разных СУБД триггер запускается в разное время: перед запросом или после него. Синтаксис также зависит от конкретной СУБД. Ниже приведём реализацию триггера, автоматически переводящего значение столбца student_country в верхний регистр при создании или обновлении записи о студенте. Синтаксис для PostgreSQL и Oracle:

CREATE TRIGGER student_country_upper
ALTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Students
SET student_country = Upper(student_country)
WHERE Students.student_id = :OLD.student.id
END;

И для примера синтаксис SQL Server:

CREATE TRIGGER student_country_upper
ON Students
FOR INSERT, UPDATE
AS
UPDATE Students
SET student_country = Upper(student_country)
WHERE Students.student_id = inserted.student.id;

Сравнение триггеров и других ограничений

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

Безопасность данных

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

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

Что чаще ограничивают для повышения безопасности:

• доступ к административным функциям (создание, изменение и удаление таблиц)
• объём доступа (только к определённым таблицам или БД)
• тип доступа (только чтение или полный доступ, но только к определённым столбцам)
• способ доступа (например, только с помощью хранимых процедур или представлений)
• доступ к управлению пользовательской учетной записью

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

Административное управление ролями осуществляется с помощью команд GRANT и REVOKE, хотя многие СУБД предоставляют для этого интерактивный интерфейс, который в свою очередь работает с их же помощью.

Следующая страница. Приложения. Создаём тестовую БД
Оглавление.