Дубликаты, неопределенные значения, первичные и возможные ключи
и другие экзотические прелести языка SQL
Сергей Кузнецов
Наверное, многим знатокам языка SQL содержимое этой заметки
покажется тривиальным. Особенно тем, кто читает колонку Криса
Дейта в журнале "Database Programming and Design" (www.dbpd.com).
Поверьте, что я не конкурирую с уважаемым господином (и моим
любимым автором) Дейтом, а лишь хочу высказать свои собственные
соображения, возникшие в ходе подготовки практического курса по
языку SQL. Я занимаюсь вопросами, связанными с организацией
доступа к базам данных, уже более 20 лет, и поэтому мне самому
было странно обнаружить в языке SQL некоторые неафишируемые, но
глубоко присущие ему свойства, отстраняющие язык от классической
реляционной теории.
Дубликаты
Будем придерживаться глубоко противной, но прижившейся в мире
популистской терминологии таблиц, строк и столбцов (вместо
классической терминологии Терминология - наука о терминах. В настоящее время используется термин «терминоведение». отношений, кортежей и атрибутов).
Некуда деваться, поскольку классическая терминология не дает
возможности погрузиться в болезненные отклонения языка SQL.
Поверьте, что я не являюсь противником этого языка. Хотелось бы
лучшего, равно как хотелось бы иметь общественный строй, отличный
от коммунизма и капитализма, но мы не можем быстро изобрести
что-либо законченное и хорошее.
Язык SQL подобно языку Си (покажите мне того, кто его искренне
любит) возник вовремя. Людям нравятся компромиссы. Компромиссы
любят и создаваемые ими предметы. SQL - это классический пример
языка баз данных, с самого начала основанный на компромиссах.
Господа, Вы хотите иметь реляционный язык? OK, вот он, реляционно
полный (доказано!) язык. Господа, Вы хотите сохранить житейскую
правду жизни при ее моделировании в базах данных? OK, вот он -
язык SQL, близкий и полезный неортодоксальным пользователям баз
данных. За что боролись, то и имеем... Примером бескомпромиссного
языка был Quel. Чистый, понятный, основанный исключительно на
реляционном исчислении кортежей. Его не приняли.
Да, язык SQL действительно реляционно полон. То есть, на нем
можно выразить любое выражение классической реляционной алгебры
или любую формулу реляционного исчисления. Но, к сожалению, на
языке SQL можно сказать гораздо больше, и к какой языковой группе
относятся расширенияРасширение имени файла (англ.filename extension, часто говорят просто расширение файла или расширение)— последовательность символов, добавляемых к имени файла и предназначенных для идентификации типа (формата) файла. Это один из распространённых способов, с помощью которых пользователь или программное обеспечение компьютера может определить тип данных, хранящихся в файле. - остается непонятным...
Прежде всего это касается возможности таблиц (в модели SQL)
содержать строки-дубликаты. На первый взгляд это кажется мелочью.
На самом деле это принципиально. Вся реляционная теория основана
на теории множеств. Математические множества не допускают наличия
дублирующих элементов. Поэтому в теории реляционных баз данных
отношения, которые определяются как множестваМножество— один из ключевых объектов математики, в частности, теории множеств и логики. кортежей, не могут
содержать кортежи-дубликаты. И дальше все получается прекрасно.
Строится алгебра или исчисление, которые замкнуты относительно
понятия отношения: результат вычисления алгебраического выражения
или интерпретации логической формулы является отношением,
следовательно, везде, где можно использовать хранимое отношение,
можно использовать выражение или формулу. Именно из
фундаментального свойства отсутствия дубликата следует наличие
возможного (а следовательно, и первичного) ключа отношения (быть
может, нескольких возможных ключей).
С другой стороны, требование отсутствия дубликатов в некоторых
случаях приводит к утрате части информации при выполнении
алгебраАлгебра (от араб. , «аль-джабр»— восполнение)— раздел математики, который можно грубо охарактеризовать как обобщение и расширение арифметики. Слово «алгебра» также употребляется в названиях различных алгебраических систем. В более широком смысле под алгеброй понимают раздел математики, посвящённый изучению операций над элементами множества произвольной природы, обобщающий обычные операции сложения и умножения чисел.ических операций. Прежде всего это связано с операцией
проецирования отношения. Поскольку не требуется, чтобы в число
атрибутов проекции входили атрибуты, составляющие возможный ключ,
при выполнении этой операции могут возникать кортежи-дубликаты, а
поскольку в результате должно получиться отношение, то они должны
устраняться. Тем самым, при выполнении операции проекции мы,
вообще говоря, теряем информацию о количестве кортежей,
содержащих одни и те же значения в атрибутах проекции.
Поскольку язык SQL является компромиссом между требованиями
теории и практики, в нем отсутствие в таблицах дублирующих строк
не является абсолютным требованием. То есть хорошим тоном
является отказ от использования таких таблиц, но язык этого не
требует. На самом деле, отказ от полного запрета дублирующих
строк вызывает очень далеко идущие последствия.
Например, по этой причине пришлось переопределить семантику
теоретико-множественных операций над таблицами. В SQL имеются
операции UNION ALL (расширенное объединение), INTERSECT ALL
(расширенное пересечение) и EXCEPT ALL (расширенное вычитание),
выполнение которых не уничтожает строки-дубликаты в результате.
Если такая операция выполняется над таблицами T1 и T2, и
некоторый кортеж C входит в обе таблицы, причем содержит n
дубликатов в таблице T1 и m дубликатов в таблице T2, то в
результате операции T1 UNION ALL T2 будет содержаться n+m
дубликатов C, в результате операции T1 INTERSECT ALL T2 -
min(n, m) дубликатов С, а в результате T1 EXCEPT ALL T2 - max((n-m), 0)
дубликатов C. Похоже, что такая трактовка
"теоретико-мультимножественных" операций введена исключительно
для определенности и не подкрепляется какими-либо здравыми
доводами.
Но это, конечно, далеко не все последствия, которые вызвал отказ
от запрета дубликатов.
Возможные и первичные ключи
Когда читаешь описание языка SQL (например, стандарт SQL/92),
как-то не сразу обращаешь внимание, что при определении схемы
таблицы (оператор CREATE TABLE) разделы PRIMARY KEY и UNIQUE
являются необязательными. Зато сразу бросается в глаза
определение таблицы, в которой не задан первичный ключ. Лично я
впервые в своей практике столкнулся с такими таблицами в
демонстрационной базе данных pubs, поставляемой вместе с
Microsoft SQL Server. В этих таблицах, естественно, нет
дубликатов, и немного позже мы обсудим реальные причины того, что
в них не существует ни одного возможного ключа. Но сам факт
наличия подобных таблиц заставил меня по-новому оценить
необязательность разделов PRIMARY KEY и UNIQUE в определении
таблицы.
На самом деле, как мы увидим ниже, компания Microsoft могла
немного по-другому спроектировать схему базы данных pubs,
добившись того, чтобы у каждой таблицы существовал хотя бы один
возможный ключ. Более того, можно было бы потребовать
использовать только такие базовые таблицы, у которых возможный
ключ существует. Но все дело в том, что это противоречило бы
принципам ортогональности компонентов языка SQL. В описании языка
утверждается, что базовые таблицы, динамически создаваемые
таблицы (хранимые таблицы, порождаемые при выполнении оператора
выборки) и представляемые таблицы (таблицы, материализуемые
только при выполнении адресованного к ним оператора языка SQL)
могут использоваться равноправно. Даже если мы потребуем, чтобы в
таблицах, входящих в основную схему базы данных, отсутствовали
дубликаты, а тем самым существовал хотя бы один возможный ключ,
мы никогда не сможем гарантировать отсутствие дубликатов (а
следовательно, наличие возможного ключа) в порождаемых (хранимых
и представляемых) таблицах. Поскольку все виды таблиц
равноправны, неразумно требовать наличия возможного ключа у
базовых таблиц. Все это звучит логично, и в некоторой степени
оправдывает необязательность разделов PRIMARY KEY и UNIQUEПотенциальный ключ — в реляционной модели данных — подмножество атрибутов отношения, удовлетворяющее требованиям уникальности и минимальности (несократимости)..
Но с другой стороны, возможностьВозможность — направление развития, присутствующее в каждом явлении жизни; выступает и в качестве предстоящего, и вполне объяснимо рациональным путем: в каждой возможности присутствует вероятная невозможность, «возможность невозможного». Возможность не определяется познанием того, что может быть. Познание вероятностей, возможностей не всегда влияет на нашу возможность. На изучении возможности основывается, главным образом, исследование бытия и события. существования таблицы без
первичного ключа в достаточной степени противоречит здравому
смыслу. Базы данных существуют для хранения информации о реально
существующих или воображаемых объектах окружающей
действительности. В классическом реляционном подходе принято
считать, что каждый кортеж каждого отношения описывает свойстваСвойство (в философии, математике и логике)— атрибут предмета (объекта). Например, о красном предмете говорится, что он обладает свойством красноты. Свойство можно рассматривать как форму предмета самого по себе, притом, что он может обладать и другими свойствами. Свойства, следовательно, подпадают под действие парадокса Рассела и парадокса Греллинга-Нельсона.,
присущие некоторой сущности реального мира. В реальном мире не
бывает двух полностью одинаковых объектов, поэтому и любые два
кортежа любого отношения должны различаться. Как мы уже видели,
из этого следует существование возможного (и первичного) ключа.
Можно взглянуть на это и с другой точки зрения. Первичный ключ
является своего рода адресом кортежа отношения. Используя
соответствующее значениеЗначение— ассоциативная связь между знаком и предметом обозначения. (может быть, составное) мы можем сказать
системе управления базами данных, информация о каком объекте нас
интересует. Конечно, все эти здравые соображения становятся
бессмысленными, когда речь идет о таблицах со
строками-дубликатами. Самое интересное состоит в том, что если
реляционная теория наводит на мысль о том, как правильно,
корректно и полезно использовать базы данных, то язык SQL, якобы
давая пользователям большую свободу, ничего не предлагает
относительно способов полезного использования этой свободы.
Первичные ключи и неопределенные значения
Как мы обещали выше, рассмотрим реальные причины того, что в двух
таблицах базы данных pubs отсутствует первичный ключ. Для
определенности выберем одну из этих таблиц - discounts (скидки).
Схема таблицы выглядит следующим образом:
CREATE TABLE discounts
(discounttype varchar(40) NO NULL, stor_id char(4),
lowqty smallint, highqty smallint, discount float NO NULL,
FOREIGN KEY stor_id REFERENCES stores)
Как видно, строка таблицы описывает скидку, назначенную для
указанного магазина. Однако в то же время строки таблицы могут
описывать и потенциально возможные скидки, даже если они не
назначены никакому магазину (поэтому столбцы stor_id, lowqty и
highqty могут содержать неопределенные значения). С другойДругой — центральная категория современной философии. Актуализация данного понятия связана с такими событиями, как антропологический и лингвистический поворот. Другой — это не Я, тот, кто противостоит мне, находится по ту сторону меня, моих ценностей, моего мировоззрения. И вместе с тем, Другой такой же как Я: он мыслит, чувствует, ходит и т. д.
стороны, тип и размер скидки могут быть одни и те же для разных
магазинов. Тем самым, пара столбцов (discounttype и discount),
которые могли бы служить первичным ключом, на самом деле таковыми
не являются (остальные столбцы не могут входить в первичный ключ,
потому что потенциально содержать неопределенные значения). Тем
самым, мы имеем таблицу, принципиально не содержащую первичного
ключа и потому (опять же потенциально) включающую
строки-дубликаты (смысла в них нет, но ничто не запрещает их
появление).
Как мы уже говорили, компанияКомпания: (фр.compagnie — 1) общество, группа; 2) фирма, рота, экипаж корабля, театральная труппа) Microsoft могла бы сделать схему
базы данных pubs более соответствующей рецептам реляционной
теории. Например, можно было бы вместо таблицы discount сделать
две таблицы:
CREATECREATE— DDL оператор языка SQL используемый для создания объектов базы данных. Различные СУБД работают с различными объектами. TABLE discounts
(discount_id int NO NULL, discounttype varchar(40) NO NULL,
discount float NO NULL,
PRIMARY KEY (discount_id))
CREATE TABLE discountsstores
(discount_id int NO NULL, stor_id char(4) NO NULL,
lowqty smallint NO NULL, highqty smallint NO NULL,
PRIMARY KEY (discount_id, stor_id),
FOREIGN KEY (discount_id) REFERENCES discounts,
FOREIGN KEY (stor_id) REFERENCES stores)
В обеих новых таблицах с первичным ключом полный порядок. Но ведь
SQL никаким образом не стимулирует такое более правильное
проектирование баз данных. И именно потому, что в таблицах могут
содержаться дубликаты. Раз все равно могут существовать таблицы
без первичного ключа, то зачем навязывать первичные ключи при
определении таблицы. Компромиссы...
Первичные, возможные, внешние ключи, функциональные зависимости и
операция группирования
Я снова воспользуюсь примером из базы данных pubs. В базе данных
имеются таблицы sales и stores, определяемые следующим образом:
CREATE TABLE sales (stor_id char(4) NO NULL,
ord_num varchar(20) NO NULL, ord_date datetime NO NULL,
qty smallint NO NULL, payments varchar(12) NO NULL,
title_id tid NO NULL,
PRIMARY KEY (stor_id, ord_num, title_id),
FOREIGN KEY (stor_id) REFERENCES stores,
FOREIGN KEY (title_id) REFERENCES titles)
CREATE TABLE stores (stor_id char(4) NO NULL,
stor_name varchar(40), stor_address varchar(40),
city varchar(20), state char(2), zip char(5),
PRIMARY KEY (stor_id))
Возникает естественное желание выполнять запросы с
эквисоединением этих двух таблиц, получая некоторую агрегатную
информацию о заказах, выполняемых некоторыми магазинами. Примером
такого запроса может быть следующий: "Выдать названия, адреса и
общий объем заказов для магазинов, расположенных в штате
Калифорния". На языке SQL хочется написать такой запрос:
SELECTSELECT ("селект") — оператор DML языка SQL, возвращающий набор данных (выборку) из базы данных, удовлетворяющих заданному условию. stor_name, stor_address, sum(qty)
FROM stores A, sales B
WHERE state = 'CA' AND A.stor_id = B.stor_id
GROUP BY A.stor_id
Мы-то понимаем, что хотим сказать: результат соединения
группируется по значениям столбца stor_id, но мы знаем, что
идентификатору магазина в таблице stores однозначно соответствует
его название и адрес (более точно, существуют функциональные
зависимости stor_id -> stor_name и stor_id -> stor_address,
потому что stor_id - первичный ключ таблицы stores). Эти
зависимости сохраняются и в соединенной таблице (stores INNER
JOIN sales ON (stores.store_id = sales.stor_id)). Тем самым,
значения полей stor_name и stor_address являются общими
характеристиками группы c одинаковыми значениями stor_id
соединенной таблицы. Однако при попытке выполнить запрос мы
получим диагностику о недопустимости включения в список выборки
имен столбцов, не входящих в список группирования. И формально
это совершенно правильно, поскольку язык SQL не обязывает
реализацию выводить зависимости для порождаемых таблиц, и система
не имеет информации о существующих функциональных зависимостях.
Более того, язык SQL не предоставляет средств для выражения таких
функциональных зависимостей.
Мне казалось, что ситуацияСитуация— одноактность и неповторимость наступления множества событий, стечения всех жизненных обстоятельств и положений, открывающихся восприятию и деятельности человека. изменится, если мы огрубим ситуацию и
объявим столбцы stor_name и stor_address возможными ключами. Для
этого в языке SQL используется раздел UNIQUE определения таблицы.
В частности, в определении таблицы stores добавились бы строчки
UNIQUE (stor_name),
UNIQUE (stor_address)
Теперь уже системаСистема (от др.-греч. — «сочетание»)— множество взаимосвязанных элементов, обособленное от среды и взаимодействующее с ней, как целое. имеет полную информацию об однозначном
соответствии значений столбцов stor_id, stor_name и stor_address.
Однако запрос по-прежнему не выполняется, и диагностика
сохраняется той же самой. И опять-таки это формально правильно,
поскольку в спецификациях языка четко сказано, что если в запросе
используется раздел GROUP BY, то в списке выборки могут
участвовать только имена столбцов, входящих в списокСписок— письменный перечень, число, состав; документ, содержащий перечень каких-либо сведений; в переносном смысле— буквальное, точное воспроизведение, копия; рукописная копия древнего памятника письменности.
группирования, и агрегатные функции, применяемые к другим
столбцам таблиц, которые перечислены в разделе FROM. Но
пользователям от этого не легче. Похожие таблицы очень вероятно
используются в практических базах данных, и запросы, подобные
приведенному выше, вполне естественны. Для того, чтобы получить
запрос, правильно воспринимаемый системой, его придется
переформулировать следующим образом:
SELECT stor_name, stor_address, sum(qty)
FROM stores A, sales B
WHERE state = 'CA' AND A.stor_id = B.stor_id
GROUP BY A.stor_id, A.stor_name, A.stor_address
В общем-то ничего страшного, можно написать и так. Но, во-первых,
это неестественно: мы вторично сообщаем системе то, что уже было
сказано при создании таблицы stores. Во-вторых, если совершенно
очевиден способМетод (от греч. — «способ»)— систематизированная совокупность шагов, действий, которые необходимо предпринять, чтобы решить определенную задачу или достичь определенной цели. В отличие от области знаний или исследований, является авторским, то есть созданным конкретной персоной или группой персон, научной или практической школой. В силу своей ограниченности рамками действия и результата, методы имеют тенденцию морально устаревать, преобразовываясь в другие методы, развиваясь в соответствии с временем, достижениями технической и научной мысли, потребностями общества. Совокупность однородных методов принято называть подходом. Развитие методов является естественным следствием развития научной мысли. выполнения операции группирования по столбцу
stores.stor_id (поскольку это первичный ключ, то в любой СУБД для
столбца stor_id будет создан уникальный индекс), то не очень
понятно, как будет выполняться модифицированный запрос
(оптимизатор сможет выбирать стратегии сортировкиАлгоритм сортировки — это алгоритм для упорядочения элементов в списке. В случае, когда элемент списка имеет несколько полей, поле, служащее критерием порядка, называется ключом сортировки. На практике в качестве ключа часто выступает число, а в остальных полях хранятся какие-либо данные, никак не влияющие на работу алгоритма. соединенной
таблицы по значениям всех трех полей группирования, а также
использования одного из трех индексов; поскольку мы объявили
stor_name и stor_address возможными ключами, то для этих столбцов
тоже вполне вероятно будут созданы уникальные индексы).
И последнее в этой заметке наблюдение. Если классики реляционной
теории говорят, что в любом отношении должен существовать хотя бы
один возможный ключ, а первичный ключ выбирается неформализуемым
образом из набора возможных ключей, то в языке SQL это не так. В
соответствии со стандартом, первичный ключ не может содержать
неопределенных значений, а ключи, объявляемые с использованием
спецификации UNIQUE, - могут содержать неопределенные значения.
Следовательно, вообще говоря в таблице SQL-ориентированной базы
данных может существовать возможный ключ и одновременно не
существовать первичного ключа. Кстати, именно так обстоит дело в
исходным вариантом таблицы discounts (если еще раз внимательно
посмотреть на ее определение, то можно убедиться, что возможный
ключ в смысле языка SQL в этой таблице существует).
Вывод, который мне хотелось бы сделать, совпадает с призывом
одного из прогрессивных деятелей: "Люди, будьте бдительны!" (даже
если вы пользуетесь стандартным языком баз данных SQL).
|
