Ограничения реляционной БД "Живопись на марках"
Ограничения позволяют поддерживать базу данных в согласованном состоянии.
К декларативным ограничениям относят
- ограничения первичных и потенциальных ключей (целостность сущностей);
- ограничения внешних ключей (ссылочная целостность);
- проверочные ограничения (ограничения доменов).
В рассматриваемой схеме первычными ключами являются идентификаторы сущностей (синтетические ключи). Наряду с ними можно (и нужно!) использовать потенциальные ключи, имеющие смысл с точки зрения моделируемой предметной области. Фактически эти ключи обеспечат целостность сущностей, выражающуюся в отсутствии дубликатов. Например, если название музея является уникальным, то такое ограничение можно создать следующим образом:
ALTER TABLE Museums ADD CONSTRAINT unique_museum_name UNIQUE(name);
Однако несмотря на то, что имя художника является уникальным, нам не удастся создать аналогичное ограничение для таблицы Artists. Проблему создают неизвестные художники, для которых столбец name содержит NULL-значение. Ограничение UNIQUE допускает наличие NULL-значений, но такое значение должно быть только одно. У нас же неизвестных художников оказалось 10. Решить проблему уникальности нам поможет уникальный индекс, синтаксис которого позволяет задать предикат, которому должны удовлетворять уникальные значения. В нашем случае таким предикатом является name IS NOT NULL. Итак,
CREATE UNIQUE INDEX uix_artists_name ON Artists(name) WHERE name IS NOT NULL;
Кроме обеспечения целостности сущностей данный индекс будет эффективно использоваться при поиске художника по имени.
В отличие от имени художника название картины уникальным не является. Картины, посвященные одним и тем же событиям или лицам, вполне могут называться одинаково, не говоря уже об автопортретах. И даже составной ключ {название картины, ИД художника} не является уникальным в нашей базе. Так, например, Перов, по крайней мере, дважды рисовал автопортрет. Для таблицы Paints естественным ключом может служить столбец stamp, содержащий путь к файлу изображения марки. Он не очень надежен, т.к. копии изображений (которые не контролируются СУБД) могут привести к дубликатам строк в таблице. Но, с другой стороны, никакая комбинация из имеющихся столбцов помимо stamp в принципе не может быть уникальной, поскольку на одну и ту же картину может быть выпущено несколько марок.
Лучшим решением здесь может оказаться добавление столбца с номером марки по некоторому каталогу.
Проверочные ограничения определяют допустимые значения, которые могут находиться в столбце (столбцах) таблицы. Фактически ограничением домена является проверочное ограничение уровня атрибута. Например, мы могли бы ограничить художественную направленность музеев только следующими типами:
Зарубежная живопись
Зарубежное искусство
Русская живопись
Русская живопись и скульптура
Русское и Зарубежное искусство
Русское искусство
ALTER TABLE Museums ADD CONSTRAINT m_types CHECK(orientation IN(
'Зарубежное искусство', 'Русская живопись', 'Русская живопись и скульптура',
'Русское и Зарубежное искусство', 'Русское искусство', 'Зарубежная живопись')
);
Ограничение уровня кортежа содержит ссылки на несколько столбцов таблицы. Примером может служить ограничение, утверждающее, что год рождения художника должен предшестовать году его смерти:
ALTER TABLE Artists ADD CONSTRAINT birth_death CHECK(birth < death);
Это ограничение будет проверяться при изменении каждого из столбцов - birth и death.
Более сложным является ограничение, которое будет проверять, чтобы год рождения художника предшествовал году создания им картины. В этом ограничении задействованы две таблицы - Paints и Artists. Кроме того, в ограничении CHECK нельзя использовать подзапросы (стандарт этого не запрещает, но я не знаю СУБД, которая бы их поддерживала). Известный приём заключается в использовании скалярных функций пользователя - UDF. Давайте напишем функцию birth_creation(), которая будет возвращать 0, если не существует художника, написавшего картину до своего рождения, и 1 - в противном случае.
CREATE FUNCTION birth_creation()
RETURNS INT
AS
BEGIN
DECLARE @res INT
SELECT @res=CASE WHEN EXISTS(
SELECT artist_id, paint_id, creation, birth FROM
(SELECT id paint_id, artist_id, CAST(creation AS INT) creation FROM Paints WHERE ISNUMERIC(creation)=1) p JOIN
(SELECT id, birth from Artists WHERE birth>0) a ON p.artist_id=id
WHERE creation <= birth) THEN 1 ELSE 0 END
RETURN @res
END;
Эта функция предварительно "фильтрует" данные, рассматривая только тех художников, год рождения которых известен, и только картины с известным годом создания.
Теперь создадим ограничение для таблицы Paints:
ALTER TABLE Paints ADD CONSTRAINT birth_before_paint CHECK(dbo.birth_creation() = 0);
Не получилось! Имеющиеся данные нарушают создаваемое ограничение. Проблему создают БРЕЙГЕЛЬ П. мл. и его картина "Поклонение волхвов". Пока специалист в предметной области будет разбираться с данными, создадим ограничение без проверки имеющихся данных:
ALTER TABLE Paints WITH NOCHECK ADD CONSTRAINT birth_before_paint CHECK(dbo.birth_creation() = 0);
Теперь это ограничение будет работать только при изменении данных в таблице Paints. Но если мы нарушим ограничение, изменив данные в таблице Artists, это изменение останется "невидимым" для нашего контроля! Для подобных случаев стандарт предоставляет глобальное ограничение уровня схемы ASSERTION, которое проверяется при изменении любых объектов, на которые в ограничении имеются ссылки. Но, как и в случае с подзапросами в CHECK, я не знаю реализации, которая бы поддерживала ASSERTION.
В нашем случае таблицы всего две, поэтому мы можем выйти из ситуации, создав аналогичное ограничение также и на таблице Artists:
ALTER TABLE Artists WITH NOCHECK ADD CONSTRAINT paint_after_birth CHECK(dbo.birth_creation() = 0);
Реляционная схема БД "Живопись на марках"