Расширенные возможности SQL
22. Расширенные возможности SQL
22.1. Что такое ограничения
Реляционные базы данных хранят данные во многих таблицах, каждая из которых данные, связанные с данными из других таблиц. Для создания ссылок из одной таблицы на другие используются ключи, отсюда термин целостность на уровне ссылок..
Чтобы реляционная база данных работала правильно, необходимо удостовериться в том, что данные в ее таблицы введены правильно.
Причины, по которым необходима проверка целостности.
· Если правила, обеспечивающие целостность базы данных осуществляются на клиентском уровне, то их придется выполнять каждому клиенту.
· Придется принудительно вводить правила для выполнения операторов UPDATE и DELETE.
· Выполнение проверок на клиентской стороне – процесс, отнимающий много времени.
Заставить СУБД выполнять эти проверки - метод более эффективный. СУБД принудительно обеспечивают целостность на уровне ссылок за счет ограничений, налагаемых на таблицы базы данных.
Рекомендуемые материалы
Ограничения – это правила, регламентирующие ввод данных в базу данных и манипулирование ими.
Большинство ограничений вводятся в определениях таблиц с помощью операторов CREATE TABLE или ALTER TABLE.
Существует несколько типов ограничений, и каждая СУБД обеспечивает свой уровень их поддержки.
22.2. Первичные ключи
Первичный ключ – это особое ограничение, применяемое для того, чтобы значения в столбце или наборе столбцов были уникальными и никогда не изменялись. Это облегчает непосредственное манипулирование отдельными строками и взаимодействие с ними. Без первичных ключей было бы очень трудно обновлять или удалять определенные строки, не задевая при этом другие.
Любой столбец таблицы может быть назначен на роль первичного ключа, но только если он удовлетворяет следующим условиям.
· Никакие две строки не могут иметь одно и то же значение первичного ключа.
· Каждая строка должна иметь какое-то значение первичного ключа, в таких столбцах не должно быть разрешено использование значений NULL.
· Столбец, содержащий значение первичного ключа, не может быть модифицирован или обновлен.
· Значения первичного ключа ни при каких обстоятельствах не могут быть использованы повторно. Если какая-то строка удалена из таблицы, ее первичный ключ не может быть назначен какой-то другой строке.
Одним из способов определения первичных ключей является их создание – в определение таблицы добавляется ключевое слово PRIMARY KEY, которое указывает, что столбец становится первичным ключом.
Пример.
CREATE TABLE Vendors
(
vend_id CHAR(10) NOT NULL PRIMARY KEY,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) NULL,
vend_city CHAR(50) NULL,
vend_state CHAR(5) NULL,
vend_ZIP CHAR(10) NULL,
vend_country CHAR(50) NULL
);
Столбец vend_id становится первичным ключом.
Пример использования другого синтаксиса.
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);
Этот синтаксис может быть использован и в операторе CREATE TABLE.
22.3. Внешние ключи
Внешний ключ – это столбец одной таблицы, значения которого совпадают со значениями столбца, являющегося первичным ключом другой таблицы. Внешние ключи – очень важная часть механизма обеспечения ссылочной целостности данных.
В базе данных примера внешним ключом является столбец идентификаторов клиента в таблице заказов Orders, содержащихся в первичном ключе таблицы клиентов Customers, т.е. этот столбец может принимать только значения, имеющиеся в первичном ключе таблицы Customers.
Один из способов определения внешнего ключа.
CREATE TABLE Orders
(
order_num INTEGER NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL REFERENCES Customers (cust_id)
);
Здесь используется ключевое слово REFERENCES для утверждения того факта, что любое значение в столбце cust_id должно быть также и в столбце cust_id таблицы Customers.
Можно использовать другой синтаксис.
ALTER TABLE Customers
ADD CONSTRAINT
FORSIGN KEY (cust_id) REFERENCES Customers (cust_id);
В дополнение к тому, что внешние ключи помогают принудительно сохранять целостность ссылочных данных, они могут выполнять много других функций.
После того как внешний ключ определен, СУБД не позволит удалять строки, связанные со строками в другизх таблицах. Нельзя удалить информацию о клиенте, у которого есть заказы. Единственный способ удалить информацию о таком клиенте состоит в предварительном удалении связанных с ним заказов (для чего , в свою очередь, нужно удалить информацию о предметах этих заказов).
В некоторых СУБД поддерживается возможность каскадного удаления, т.е. можно удалять все связанные с этой строкой. Например, при каскадном удалении имени клиента из таблицы Customers, все связанные с его заказом строки удаляются автоматически.
22.4. Ограничение уникальности
Ограничения уникальности обеспечивают уникальность всех данных в столбце или в наборе столбцов. Такие столбцы похожи на первичные ключи, но имеются и важные отличия.
· Таблица может содержать множество ограничений уникальности, но у нее должен быть только один первичный ключ.
· Столбцы с ограничением уникальности могут содержать значения NULL.
· Столбцы с ограничением уникальности можно модифицировать и обновлять.
· Значениея столбцов с ограничением уникальности можно использовать повторно.
· В отличие от первичных ключей, ограничения уникальности не могут быть использованы для определения внешних ключей.
Примером использования ограничения может служить таблица с данными о служащих. Каждый из них имеет свой уникальный номер карточки социального страхования, но вряд ли стоит использовать его в качестве первичного ключа, поскольку он слишком длинный. Поэтому каждому служащему присваивается уникальный идентификатор (первичный ключ) в дополнение к его номеру карточки социального страхования.
Поскольку идентификатор служащего является первичным ключом, можно быть уверенным, что он уникален. Для того чтобы СУБД проверяла уникальность каждого номера карточки социального страхования (при вводе нельзя указывать одинаковые номера карточек), нужно определить ограничение UNIQUE для столбца, в котором содержатся номера карточек социального страхования.
Синтаксис ограничения на уникальность похож на синтаксис других ограничений: при определении таблицы указывается ключевое слово UNIQUE или отдельно используется ограничение CONSTRAINT/
22.5. Ограничения на значения столбца
Ограничения на значения столбца используются для того, чтобы данные в столбце или наборе столбцов соответствовали ряду определенным критериям. Наиболее часто используемыми являются:
· Ограничения максимального и минимального значений – например, для предотвращения появления заказов на 0 предметов (хотя 0 и является допустимым числом).
· Указание диапазонов – например, ограничение на то, чтобы дата отгрузки наступала позже или соответствовала текущей дате и не отстояла от нее больше, чет на год.
· Разрешение только определенных значений – например, разрешить вводить в поле «пол» только буквы М или Ж.
Типы данных ограничивают типы данных, которые могут храниться в столбце. Ограничения на значения столбца предъявляют дополнительные требования уже к данным определенного типа.
В примере накладываются ограничения на значения столбцов таблицы OrderItems с тем, чтобы для всех предметов указывалось количество, больше 0.
CREATE TABLE OrderItems
(
ordr_num Integer NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL CHECK(quantity > 0),
item_price MONEY NOT NULL
);
После применения этого ограничения каждая добавляемая или обновляемая строка будет проверяться на предмет тог, что количество предметов больше нуля.
Чтобы проконтролироваться тот факт, что в столбце с наименованием пола может содержаться только буква М или Ж, можно сделать следующее в операторе ALTER TABLE.
ADD CONSTRAINT CHEK (gender LIKE ‘[МЖ]’)
22.6. Что такое индексы
Индексы используются для логической сортировки данных с целью повышения скорости поиска и выполнения в последующем операцией сортировки.
Данные первичного ключа всегда отсортированы. Выборка указанных строк по первичному ключу всегда гарантирует быстроту и эффективность этой операции.
Поиск значений в других столбцах выполняется уже не столь эффективно. При выборке всех клиентов, проживающих в определенном штате, придется читать каждую строку таблицы, отыскивая соответствия.
Решение этой проблемы состоит в использовании индекса. Можно определить в качестве индекса один или несколько столбцов так, чтобы СУБД хранила отсортированный список содержимого. После того как индекс определен, СУБД использует его точно так же, как использует человек предметный указатель книги. СУБД производит поиск в отсортированном индексе, чтобы найти местоположение всех соответствий и выбрать эти строки.
При создании множества индексов необходимо учитывать следующее.
· Индексы повышают производительность операций выборки, но ухудшают производительность при выполнении таких операций, кА добавление данных, их модификация и удаление. Вызвано это тем, что при выполнении подобных операций СУБД должна еще и динамически обновлять индекс.
· Для хранения данных индекса требуется много места на жестком диске.
· Не все данные подходят для индексации. Данные, которые не являются по сути уникальными, например названия штатов, не дадут такого выигрыша от индексации, как данные, которые имеют больше возможных значений, например фамилии.
· Индексы используются для фильтрации и сортировки данных. Если часто сортируются данные определенным образом, эти данные могут быть кандидатом на индексацию.
· В качестве индекса можно определить несколько столбцов, напрмер штата и названия города. Такой индекс можно использовать, только если данные будут отсортированы в порядке «штат+город». Если отсортировать данные по названию города, такой индекс использован не будет.
В большинстве СУБД предлагаются утилиты, которые можно использовать для определения эффективности индексов.
Индексы создаются с помощью оператора CREATE INDEX различный для разных СУБД.
Пример создания простого индекса для столбца с наименованием продукта таблицы Products.
CREATE INDEX prod_name_ind
ON Products (prod_name);
Каждый индекс должен иметь уникальное имя.
22.7. Что такое триггеры
Триггеры – это особые хранимые процедуры, автоматически выполняемые при использовании базы данных определенным образом. С любой операцией, вызывающей изменение содержимого таблицы, можно связать сопутствующее действие (триггер), которое СУБД должна выполнять при выполнении каждой такой операции. Триггеры могут быть связаны с выполнением операций INSERT, UPDATE и DELETE.
В отличие от хранимых процедур, которые представляют собой просто хранимые операторы SQL, триггеры привязаны к отдельным таблицам. Триггер, ассоциирующийся с операциями INSERT по отношению к таблице Orders, будет выполняться только в том случае, если строка добавляется в таблицу Orders.
Будучи примененным в триггере, код может иметь доступ к следующим данным:
· Ко всем новым данным в операциях INSERT;
· Ко всем новым и старым данным в операция UPDATE;
· К удаляемым данным в операциях DELETE.
Чаще всего триггеры используются:
· Для обеспечения непротиворечивости данных;
· Для выполнения действий по отношению к другим таблицам, основанных на изменениях, которые были сделаны в какой-то таблице, например, для внесения записи в контрольный журнал с целью регистрации каждого случая обновления или удаления строки;
· Для выполнения дополнительной проверки и отмены введения данных, например, чтобы удостовериться в том, что разрешенная для клиента сумма кредита не превышена, в противном случае операция блокируется;
· Для подсчета значений вычисляемых полей или обновления меток даты/времени.
В примере создается триггер, преобразующий значения столбца cust_state в таблице Customers в верхний регистр при выполнении любых операций INSERT и UPDATE.
Для SQL Server/
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;
Для Oracle
CREATE TRIGGER customer_state
AFTER INSERT, UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id
END;
Как правило, ограничения обрабатываются быстрее, чем триггеры.
22.7. Безопасность баз данных
В основе любой системы безопасности лежат авторизация и аутентификация пользователей. Так называется процесс, в ходе которого пользователь подтверждает, что он – это именно он и что ему разрешено производить операции, которые он собирается выполнять.
Некоторые СУБД используют для этого средства безопасности операционной системы, другие ведут собственные списки пользователей и паролей, третьи интегрируются с внешними серверами служб каталогов.
Часто используются следующие механизмы безопасности:
Люди также интересуются этой лекцией: 2 - Ареалогия.
· Ограничение доступа к механизмам управления базой данных.
· Ограничение доступа к отдельным базам данных или таблицам.
· Ограничение типа доступа – только для чтения, доступ к отдельным столбцам.
· Организация доступа к таблицам только через представления или хранимые процедуры.
· Создание нескольких уровней безопасности, вселедствие чего обеспечивается различная степень доступа и контроля на основе регистрационного имени пользователя.
Ограничение возможности управлять учетными записями
