Обеспечение целостности данных и хранимые процедуры

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

В таблицах SQL Server может быть определен ряд свойств различного типа, обеспечивающих целостность данных. К ним относятся типы данных, определения NOT NULL и DEFAULT , свойства IDENTITY , ограничения, правила, триггеры и индексы.

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

Обеспечение целостности данных гарантирует их качество. Предположим, что мы создали в базе данных таблицу Persons . Значение столбца PersonId должно уникально идентифицировать каждую персону, сведения о которой занесены в таблицу. Таким образом, если значение PersonId некоей персоны равно 834, то ни для какой другой персоны это значение не может быть таким же. Далее предположим, что имеется столбец PersonRating , в котором определяется рейтинг персон - в диапазоне от 1 до 10. В этом случае столбец PersonRating не должен допускать ввода ни числа 11, ни каких-либо иных значений, кроме чисел из интервала от 1 до 10. В обоих случаях для обеспечения целостности данных следует применять один из методов, поддерживаемых SQL Server.

Среди методов SQL Server, предназначенных для обеспечения целостности данных, - определения NOT NULL и DEFAULT , свойства IDENTITY , ограничения, правила, триггеры и индексы. Некоторые из них уже упоминались, здесь же приводится их краткое описание. Некоторые свойства таблицы, например определения NOT NULL и DEFAULT , иногда считают ограничениями особого типа. Однако в соответствии с задачами нашего курса, они рассматриваются отдельно от ограничений.

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

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

Задавая возможность ввода в поле пустых значений, мы определяем, могут ли в этом столбце таблицы храниться пустые значения. Пустое значение отличается от нуля, пробела или символьной строки нулевой длины, например «». Пустое значение означает, что информация не введена, то есть значение не известно или не определено. Возможность ввода в столбец пустых значений задается при определении этого столбца во время создания таблицы или ее модификации. Из-за сложностей, связанных с обработкой пустых значений в SQL Server, в определении столбцов как допускающих, так и не допускающих ввода пустых значений всегда следует использовать ключевые слова NULL или NOT NULL . Если столбец допускает пустые значения, используется ключевое слово NULL , если нет - NOT NULL .

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

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

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

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

Сначала следует создать правило с помощью оператора CREATE RULE . После этого при помощи системной хранимой процедуры sp_bindrule его привязывают к столбцу или пользовательскому типу данных. Подробнее об использовании CREATE RULE или sp_bindrule - в справочнике по Transact-SQL в SQL Server Books Online.

Триггеры - это особый класс хранимых процедур, автоматически запускаемых при выполнении операторов UPDATE , INSERT или DELETE для таблицы или представления. Триггеры представляют собой мощный инструмент, применяемый для автоматической реализации бизнес-логики при модификации данных. Триггеры способны расширить логику проверки целостности, реализуемую ограничениями, умолчаниями и правилами SQL Server (хотя во всех случаях, когда ограничения способны обеспечить необходимую функциональность, следует использовать их вместо триггеров).

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

Типы целостности данных

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

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

Сущностная целостность

Этот тип целостности определяет строку таблицы как уникальный экземпляр некоторой сущности. Сущностная целостность обеспечивает целостность столбца с идентификатором или первичного ключа таблицы (посредством индексов, ограничений UNIQUE , PRIMARY KEY или свойств IDENTITY ).

Доменная целостность

Этот тип целостности гарантирует наличие в некотором столбце только допустимых значений. Можно обеспечивать доменную целостность, ограничивая тип (посредством типов данных), формат (с помощью ограничений CHECK и правил) или диапазон допустимых значений (с помощью ограничений FOREIGN KEY и CHECK, определений DEFAULT, определений NOT NULL и правил).

Ссылочная целостность

Этот тип целостности обеспечивает сохранность связей между таблицами при добавлении или удалении записей. В SQL Server ссылочная целостность основана на связях между внешними и первичными ключами или между внешними и уникальными ключами (через ограничения FOREIGN KEY и CHECK ). Ссылочная целостность гарантирует согласованность значений ключа в связанных таблицах. Подобная согласованность требует отсутствия ссылок на несуществующие значения и согласованного изменения ссылок на ключ во всей базе данных при изменении самого ключа.

При обеспечении ссылочной целостности SQL Server предотвращает следующие действия пользователей:

Добавление записей в связанную таблицу, если нет необходимой записи в главной таблице;

Изменение значений в главной таблице, в результате которого в связанной таблице останутся «зависшие» записи;

Удаление записей из главной таблицы при наличии связанных записей во внешней таблице.

Конструкция FROM

Конструкцию FROM необходимо помещать в каждом операторе SELECT , который извлекает данные из таблиц или представлений. Эта конструкция позволяет задать список таблиц и представлений, на столбцы которых ссылаются список выбора и конструкция WHERE . Этим таблицам и представлениям могут быть присвоены псевдонимы в конструкции AS . Конструкция FROM , кроме того, позволяет соединять таблицы, задавая условия соединения в конструкции JOIN .

Конструкция FROM представляет собой список имен таблиц, представлений и конструкций JOIN , разделенных запятыми. В следующем примере в операторе SELECT конструкция FROM задает таблицу Persons :

SELECT * FROM Persons

Конструкцию FROM также используют и для определения соединений между двумя таблицами или представлениями. О соединениях более подробно рассказано в следующем разделе.

Конструкции WHERE, GROUP BY и HAVING

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

SELECT PersonId, LastName, FirstName

WHERE BirthPlace = "Krasnoyarsk"

Конструкция HAVING , как правило (но не обязательно), используется вместе с конструкцией GROUP BY . Конструкция HAVING задает дополнительные фильтры, которые применяются после завершения фильтрации, определяемой конструкцией WHERE . В следующем сценарии в операторе SELECT использованы конструкции WHERE , GROUP BY и HAVING :

SELECT OrdD1OrderId AS OrderId,

SUM(OrdD1.Quantity) AS "Units Sold",

SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue

FROM AS OrdD1

WHERE OrdD1OrderId IN (SELECT DISTINCT OrdD2.OrderId

FROM AS OrdD2

WHERE OrdD2.UnitPrice > $1000)

GROUP BY OrdD1.OrderId

HAVING SUM(OrdD1.Quantity) > 50

Здесь конструкция WHERE возвращает заказы, стоимость которых больше $1000, а далее конструкция HAVING ограничивает результат, отбирая заказы на более чем 50 единиц товара. Конструкция GROUP BY ограничивает строки для каждого конкретного значения поля OrderId .

Конструкция GROUP BY

Конструкция GROUP BY используется для получения итоговых значений в каждой строке результирующего набора. При применении оператора SELECT без конструкции GROUP BY агрегатные функции возвращают лишь одно итоговое значение.

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

Если в операторе SELECT имеется конструкция GROUP BY , SQL Server налагает ограничения на элементы списка выбора. В списке выбора могут быть лишь те группирующие столбцы и выражения, которые возвращают только одно значение для каждого значения группирующих столбцов, например агрегатные функции (векторные агрегаты), одним из параметров которых является имя столбца.

Как правило, конструкция HAVING используется с конструкцией GROUP BY , хотя ее разрешается применять и отдельно. Любое выражение может стать группирующим, если оно не содержит агрегатных функций.

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

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

Понимание верной последовательности, в которой применяются конструкции WHERE , GROUP BY и HAVING , помогает создавать достаточно эффективные запросы:

Конструкция WHERE фильтрует строки, которые являются результатом операций, заданных в конструкции FROM;

Выходная информация конструкции WHERE группируется с помощью конструкции GROUP BY;

Строки сгруппированного результата фильтруются средствами конструкции HAVING .

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

Конструкция ORDER BY

Конструкция ORDER BY сортирует результат запроса по одному или нескольким полям. Сортировка может быть как по возрастанию (ASC ), так и по убыванию (DESC ). Если не задан ни один из видов сортировки, по умолчанию предполагается ASC . Если в конструкции ORDER BY названо несколько столбцов, выполняется вложенная сортировка.

Следующий оператор сортирует строки таблицы Persons сначала по фамилии (по убыванию), затем по имени (по возрастанию, для каждой персоны) и, наконец, по возрасту (также по возрастанию, поскольку не задан параметр DESC ).

SELECT PersonId, LastName, FirstName, Age

ORDER BY LastName DESC, FirstName, Age

Пакеты, хранимые процедуры и триггеры

Пакет - это группа из одного или нескольких операторов Transact-SQL, которые приложение одновременно посылает на SQL Server для исполнения. SQL Server компилирует операторы пакета в единую исполнимую единицу (план исполнения – Execution Plan ). После этого по очереди выполняются операторы этого плана.

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

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

Большинство ошибок периода выполнения останавливают исполнение текущего и последующих операторов пакета;

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

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

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

При обработке пакетов действуют следующие правила:

Операторы CREATE DEFAULT , CREATE PROCEDURE , CREATE RULE , CREATE TRIGGER и CREATE VIEW не могут соседствовать в пакетах с другими операторами. Пакет должен начинаться с оператора CREATE . Все следующие за ним операторы будут интерпретированы как часть определения, созданного первым оператором CREATE ;

В пределах одного и того же пакета нельзя модифицировать таблицу и обращаться к новым столбцам;

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

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

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

Все операторы SQL, которые приложение отправляет на сервер как единицу исполнения, составляют единый пакет и генерируют один план исполнения.

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

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

Строка, исполняемая системной хранимой процедурой sp_executesql , - это пакет, при компиляции которого получается один план исполнения.

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

Если оператор пакета вызывает триггер, то план исполнения триггера выполняется отдельно от плана исполнения исходного пакета.

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

Оператор EXECUTE , исполняющий хранимую процедуру;

Вызов процедуры sp_executesql для обработки строки;

Оператор EXECUTE , обрабатывающий строку;

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

EXEC FirstProcedure

EXEC sp_executesql N"SELECT * FROM AdventureWorks.HumanResources.Employee

WHERE ManagerID = @level",

N"@level tinyint",

SET PersonName = "kuku"

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

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

Операторы CREATE PROCEDURE и CREATE TRIGGER не могут располагаться в нескольких пакетах. Другими словами, хранимая процедура или триггер всегда создаются в одном пакете и компилируются в план исполнения.

SQL Server хранит только исходный текст хранимых процедур и триггеров. Когда хранимая процедура или триггер исполняется первый раз, исходный текст компилируется в план исполнения. Если до того, как план исполнения устареет и будет удален из памяти, хранимая процедура или триггер исполняется снова, реляционный механизм обнаруживает существующий план и использует его повторно. Если план устарел и удален из памяти, создается новый план исполнения. Этот процесс напоминает обработку SQL Server всех операторов SQL. Увеличение производительности при применении хранимых процедур и триггеров объясняется постоянством их SQL-операторов, что позволяет SQL Server использовать для них существующие планы исполнения.

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

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

Целостность базы данных — эта практика будет гарантировать, что данные, хранящиеся в базе данных, являются точными, последовательными и свободными от ошибок. Малейшая ошибка в правописании или в повторении информации может вызвать огромные проблемы для базы данных, поэтому целостность базы данных — это серьезный вопрос. Есть три ограничения целостности — целостность сущности, целостность домена и ссылочная целостность. Наряду с этими тремя ограничениями, для обеспечения целостности применяются нормализация и проверка на ошибки. Большинство этих процессов автоматизировано, но администраторы часто также проверяют вручную, чтобы убедиться в отсутствии ошибок в базе данных.
Когда администратор хочет добавить, изменить или удалить информацию из базы данных, должна быть проверена целостность базы данных. Включение или изменение любой информации может иметь катастрофические последствия, поскольку эти данные могут иметь далеко идущие последствия для других таблиц и разделов базы данных. Чтобы снизить вероятность этого, и держать уже имеющуюся информацию в базе данных точной, герметичность всегда проверяется целостность базы данных, прежде чем могут быть сделаны любые изменения.

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

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

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

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

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

Целостность БД не гарантирует достоверности содержащейся в ней информации, но обеспечивает, по крайней мере, правдоподобность этой информации, отвергая заведомо невероятные, невозможные значения. Таким образом, не следует путать целостность БД с достоверностью БД. Достоверность (или истинность) есть соответствие фактов, хранящихся в базе данных, реальному миру. Очевидно, что для определения достоверности БД требуется обладание полными знаниями как о содержимом БД, так и о реальном мире. Для определения целостности БД требуется лишь обладание знаниями о содержимом БД и о заданных для неё правилах. Поэтому СУБД может (и должна) контролировать целостность БД, но принципиально не в состоянии контролировать достоверность БД. Контроль достоверности БД может быть возложен только на человека, да и то в ограниченных масштабах, поскольку в ряде случаев люди тоже не обладают полнотой знаний о реальном мире.

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

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

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

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

Обеспечение целостности данных гарантирует качество данных в таблице.

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

• 1. Сущностная целостность -- определяет строку как уникальную сущность в конкретной таблице. Она обеспечивает целостность столбцов идентификаторов или первичного ключа таблицы с помощью индексов и ограничений UNIQUE или PRIMARY KEY.
• 2. Доменная целостность -- это достоверность записей в конкретном столбце. Она включает ограничения типа данных, ограничения формата при помощи ограничений CHECK и правил, а также ограничения диапазона возможных значений при помощи ограничений FOREIGN KEY, CHECK, DEFAULT, определений NOT NULL и правил.
• 3. Ссылочная целостность -- сохраняет определенные связи между таблицами при добавлении или удалении строк.

В SQL Server ссылочная целостность основана на связи первичных и внешних ключей (либо внешних и уникальных ключей) и обеспечивается с помощью ограничений FOREIGN KEY и CHECK. Ссылочная целостность гарантирует согласованность значений ключей во всех таблицах. Этот вид целостности требует отсутствия ссылок на несуществующие значения, а также обеспечивает согласованное изменение ссылок во всей базе данных при изменении значения ключа.

При обеспечении ссылочной целостности SQL Server не допускает следующих действий пользователей:

• - Добавления или изменения строк в связанной таблице, если в первичной таблице нет соответствующей строки;
• - Изменения значений в первичной таблице, которое приводит к появлению потерянных строк в связанной таблице;
• - Удаления строк из первичной таблицы, если имеются соответствующие ей строки в связанных таблицах.
• 4. Пользовательская целостность -- позволяет определять бизнес-правила, не входящие ни в одну из категорий целостности. Поддержку пользовательской целостности обеспечивают все остальные категории целостности: любые типы ограничений уровня столбца и уровня таблицы в инструкции CREATE TABLE, хранимых процедурах и триггерах.

семантический ссылочный целостность ограничение

В ходе разработки фрагмента информационной системы в СУБД MySQL возникает задача обеспечения целостности данных. Рассмотрим само понятие целостности.

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

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

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

Семантическая целостность данных

Семантическая целостность данных - необходимое условие функционирования информационной системы любого типа. Приведем перечень основных ограничений семантической целостности. В качестве примера выберем базу данных «Салон», состоящую из таких таблиц, как «Клиент», «Мастер», «Услуга» в данном параграфе приводятся теоретические способы реализации того или иного ограничения целостности. В главе II будут более подробно рассмотрены практические способы реализации приведенных видов ограничения целостности данных.

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

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

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

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

1) Допустимые значения находятся в заданном интервале. Данное ограничение предполагает, что значение свойства находится в определенном диапазоне значений. Допустим, в салоне красоты существует возрастной ценз работников: мастер не может быть младше 20 и старше 50 лет. Таким образом, ограничение целостности выглядит так «Для каждого объекта класса «Мастер» значение атрибута «возраст» находится в диапазоне 20-50 лет».

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

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

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

Значения свойств «фамилия», «имя», «отчество» имеют следующий вид: первый символ - прописная буква кириллицы, последующие - строчные. Недопустим ввод цифр.

Формат даты рождения клиентов и мастеров, дат посещения салона, а также отпуска или больничного, предоставляемых мастерам - ДД/ММ/ГГ.

5) Запрет на NULL - значение атрибута. Такой вид ограничения распространяется прежде всего на идентификатор объекта, являющийся первичным ключом, а также на те свойства, пустое значение которых недопустимо.

К примеру, помимо идентификатора, недопустимо пустое значение таких полей, как ФИО клиента или мастера, или отсутствие информации о предоставленных клиенту услугах при указании общей стоимости, которую заплатил клиент.

6) Ограничение на значения семантически связанных полей. В данном случае на них накладываются определенные условия или функциональные зависимости. Так, в нашей базе данных значения полей «дата начала отпуска» и «дата окончания отпуска» являются семантически связанными: первая дата является меньшей, чем вторая. В этом случае следует сформулировать ограничение целостности в следующем виде: : «Для каждого объекта заданного класса между значениями свойств С1 и С2 должно всегда выполняться условие: С1<С2».

Важным аспектом реализации ограничений семантической целостности является то, что на значение свойства могут накладываться одновременно несколько видов ограничений. Например, значение свойства «возраст» в таблице «Мастер» имеет ограничение по интервалу (20-50 лет), по формату, также возможен выбор значения из списка-интервала.

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

• существование записей-сирот (дочерних записей, не имеющих связи с родительскими записями);
• существование одинаковых первичных ключей.

Для проверки целостности данных в криптографии используются хеш-функции , например, MD5 . Хеш-функция преобразует последовательность байт произвольного размера в последовательность байт фиксированного размера (число). Если данные изменятся, то и число, генерируемое хеш-функцией , тоже изменится.

Целостность данных - свойство, при выполнении которого данные сохраняют заранее определённый вид и качество.

Определения из стандартов

Понятие «целостность объекта » (англ. integrity ) используется как термин в теории информационной безопасности (ИБ). Под объектом понимают информацию, специализированные данные или ресурсы автоматизированной системы. Целостность информации (как ресурса автоматизированной системы) является одним из трёх основных свойств объекта ИБ. Свойства объекта ИБ:

• доступность (англ. availability );
• целостность (англ. integrity );
• конфиденциальность (англ. confidentiality ).

Иногда в этот список добавляют:

• неотказуемость (англ. non-repudiation );
• подотчётность (англ. accountability );
• аутентичность или подлинность (англ. authenticity );
• достоверность (англ. reliability ).

Способы обеспечения целостности

Методы и способы реализации требований, изложенных в определениях термина, подробно описываются в рамках единой схемы обеспечения информационной безопасности объекта (защиты информации).

Основными методами обеспечения целостности информации (данных) при хранении в автоматизированных системах являются:

• обеспечение отказоустойчивости (резервирование , дублирование , зеркалирование оборудования и данных, например через использование RAID -массивов);
• обеспечение безопасного восстановления (резервное копирование и электронное архивирование информации).

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

При комплексном подходе к защите бизнеса, направление обеспечения целостности и доступности информации (ресурсов бизнес-процессов) перерастает в план мероприятий, направляемых на обеспечение непрерывности бизнеса .

Целостность данных в криптографии

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

• инверсия битов ;
• добавление новых битов (в частности совершенно новых данных) третьей стороной;
• удаление каких-либо битов данных;
• изменение порядка следования бит или групп бит.

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

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

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

Имитовставки

Число двоичных разрядов (число бит) в имитовставке в общем случае определяется криптографическими требованиями с учётом того, что вероятность навязывания ложных данных равна 1/2 p , где p - число двоичных разрядов (число бит) в имитовставке.

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

M = f (x ) ,

• M - имитовставка;
• f - функция, вычисляющая имитовставку;
• x - сообщение.

Имитовставка может использоваться как для проверки подлинности сообщения, так и для проверки его целостности. В зависимости от назначения имитовставки алгоритмы работы функций f (коды) делят на два класса:

• коды проверки целостности сообщения (MDC , англ. modification detection code ). Алгоритмы вычисляют имитовставку, пригодную для проверки целостности (но не подлинности) данных, путём хеширования сообщения;
• коды аутентификации (проверки подлинности) сообщения (MAC , англ. message authentication code ). Алгоритмы вычисляют имитовставку, пригодную для защиты данных от фальсификации, путём хеширования сообщения с использованием секретного ключа .

MDC

Хеш-функции для вычисления кода проверки целостности сообщений принадлежат к подклассу бесключевых хеш-функций . В реально существующих криптосистемах эти хеш-функции являются криптографическими , то есть кроме минимальных свойств хеш-функций (сжатие данных, простота вычисления дайджеста (англ. digest ) от сообщения) удовлетворяют следующим свойствам:

• необратимость (англ. preimage resistance );
• стойкость к коллизиям первого рода (англ. weak collision resistance );
• стойкость к коллизиям второго рода (англ. strong collision resistance ).

В зависимости от того, каким из этих свойств удовлетворяют MDC хеш-функции , можно выделить два их подкласса:

• однонаправленные хеш-функции (OWHF , от англ. one-way hash function ), которые удовлетворяют свойству необратимости и устойчивы к коллизиям первого рода;
• устойчивые к коллизиям хеш-функции (CRHF , от англ. collision resistant hash function ), которые устойчивы к коллизиям первого и второго рода (вообще говоря, на практике CRHF хеш-функции удовлетворяют и свойству необратимости).

Существует три основных типа MDC алгоритмов хеш-функций , по способу их построения:

• на блочных шифрах ; например: алгоритм Matyas-Meyer-Oseas , алгоритм Davies-Meyer , алгоритм Miyaguchi-Preneel , MDC-2 , MDC-4 ;
• специальные (англ. customized ) алгоритмы хеширования , в которых делается упор на скорость, и которые независимы от других компонент системы (в том числе блочных шифров или компонент модульного умножения, которые могут быть уже использованы для других целей). Например: MD4 , MD5 , SHA-1 , SHA-2 , RIPEMD-128 , RIPEMD-160 ;
• на модульной арифметике; например: MASH-1 , MASH-2 .

MAC

К MAC хеш-функциям для вычислений кодов аутентификации сообщений , подсемейству ключевых хеш-функций, относят семейство функций удовлетворяющих следующим свойствам:

• простота вычисления дайджеста (англ. digest ) от сообщения;
• сжатие данных - входное сообщение произвольной битовой длины преобразуется в дайджест фиксированной длины;
• стойкость ко взлому - имея одну и более пар сообщение-дайджест, (x[i], h(x[i])), вычислительно невозможно получить новую пару сообщение-дайджест (x, h(x)), для какого-либо нового сообщения x .

Если не выполняется последнее свойство, то MAC может быть подделан. Также последнее свойство подразумевает, что ключ невозможно вычислить, то есть, имея одну или более пар (x[i], h(x[i])) с ключом k , вычислительно невозможно получить этот ключ.

Алгоритмы получения кода аутентификации сообщения могут быть разделены на следующие группы по их типу:

• на блочных шифрах . Например, CBC-MAC , RIPE-MAC1 , RIPE-MAC3 ;
• получение MAC из MDC ;
• специальные (англ. customized ) алгоритмы. Например, MAA , MD5-MAC ;
• на потоковых шифрах. Например, CRC-based MAC .

Получение MAC на основе MDC

Существуют методы получения из MDC кодов аутентификации сообщений включением секретного ключа во входные данные алгоритма MDC. Недостатком такого подхода является то, что фактически на практике большинство алгоритмов MDC разработано так, что они являются либо OWHF , либо CRHF , требования к которым отличаются от требований к MAC алгоритмам.

1. secret prefix method : К последовательности блоков данных x {\displaystyle x} =x 1 x 2 x 3 ..x n в начало приписывается секретный ключ k : k ||x . Для данной последовательности данных с помощью итерационной хеш-функции вычисляется MDC, например, такой, что H 0 =IV (от англ. initial value ), H i =f (H i-1 ,x i) h (x ) = H n . Таким образом, MAC M {\displaystyle M} =h (k ||x ). Минусом такого подхода является то, что третья сторона может дописать в конец последовательности блоков дополнительные данные y : k ||x ||y . Новый MAC может быть вычислен без знания ключа k : M {\displaystyle M} 1 = f ( M {\displaystyle M} ,y ).
2. secret suffix method : Секретный ключ приписывается в конец последовательности данных: x ||k . В этом случае MAC M {\displaystyle M} =h (x ||k ). В этом случае может быть применена атака методом дней рождений . При длине дайджеста в n бит. Третьей стороне понадобится порядка 2 n/2 операций, чтобы для сообщения x найти сообщение x’ такое, что h (x )= h (x’ ). При этом знание ключа k будет не обязательно. Узнав значение MAC M {\displaystyle M} для сообщения x , третья сторона сможет сгенерировать корректную пару (x’ , M {\displaystyle M} ).
3. envelope method with padding : Для ключа k и MDC h h k (x )=(k ||p ||x ||k ), где p - строка, дополняющая ключ k до длины блока данных, для того, чтобы гарантировать, что будет произведено как минимум 2 итерации. Например, для MD5 k - 128 бит, а p - 384 бита.
4. HMAC : Для ключа k и MDC h вычисляется MAC от сообщения h k (x )=(k ||p 1 ||h (k ||p 2 ||x )), где p 1 ,p 2 - различные строки, дополняющие k до длины блока данных. Такая конструкция довольно эффективна, несмотря на двойное использование h .

Схемы использования

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

Обеспечение целостности данных с использованием шифрования и MDC

От исходного сообщения вычисляется MDC , M {\displaystyle M} =h (x ). Этот