Автонумерация не может использоваться в качестве реляционного ключа
Обоснование
Средства автонумерации представляют собой пережиток ранних версий SQL, предназначенных для систем с последовательным хранением данных — на физически смежных страницах, строках и столбцах, как в стопке перфокарт или на магнитной ленте. У большинства программистов с той поры ментальная модель данных не изменилась.
Но физически последовательная запись представляет собой лишь один из способов хранения реляционной БД, причем, далеко не лучший. Основная идея реляционной БД заключается в том, что пользователь вообще ничего не должен знать о том, как хранятся данные, не говоря уже о том, как писать код, зависящий от конкретного физического представления в конкретной версии конкретного продукта.
Первое практическое соображение таково: автонумерация является нестандартной функцией, поэтому ждите проблем при переходе на другую версию того же продукта или на другой продукт. Впрочем, новички искренне полагают, что переносить код им никогда не придется! То ли они работают на умирающую компанию, то ли считают свой код безнадежным и не верят, что он может пригодиться кому-то еще...
Но давайте обратимся к логическим проблемам. Во-первых, попробуйте создать таблицу с двумя столбцами. Можно ли задать для обоих автонумерацию? Если тип данных нельзя присвоить более чем одному столбцу, то это по определению вообще не тип данных. Это свойство физической таблицы, а не данных в ней.
Теперь создайте таблицу с одним столбцом и задайте для него автоиумерацию. Можно ли вставлять, изменять и удалять числа из этого столбца? Если строки нельзя вставлять, изменять и удалять, то это по определению не таблица.
Наконец, создайте простую таблицу с одним скрытым столбцом автонумерации и несколькими другими столбцами. Используйте операторы наподобие:
INSERT INTO Foobar (a, b, с) VALUES ('a1', 'b1', 'c1');
INSERT INTO Foobar (a, b, с) VALUES ('a2', 'b2', 'c2');
INSERT INTO Foobar (a, b, с) VALUES ('a3', 'b3', 'c3');
Вставьте в таблицу несколько строк и убедитесь, что им присвоены номера, отвечающие порядку, в котором они были вставлены. Если вы удаляете строку пробел в нумерации не заполняется. При добавлении новых строк им присваиваются номера, начиная с самого большого номера, который когда-либо появлялся в данной таблице. Между прочим, именно так создавались последовательные файлы в 1950-х годах. Затем специальная программа удаляла записи, помеченные как ненужные, чтобы переместить освободившееся пространство в физический конец физического файла.
Используем теперь выражение с запросом, например:
INSERT INTO Foobar (a, b, с)
SELECT x, у, z FROM Floob;
Результатом выполнения запроса является таблица, то есть неупорядоченный набор данных. Какова должна быть ее автонумерация? Весь набор вставляется в таблицу Foobar одновременно, а не по одной строке за раз. Имеется n! способов пронумеровать n строк, на каком вы остановитесь? В реальности используется тот физический порядок, в котором оказались записи. Опять этот нереляционный “физический порядок”!
В действительности все еще хуже. Если тот же самый запрос выполнить еще раз, но с новой статистикой или после удаления или добавления индекса, в результате нового плана выполнения набор записей может быть возвращен в другом физическом порядке. Как с точки зрения физической модели объяснить, что тем же самым строкам во втором запросе присвоены другие автоматические номера? В реляционной модели строки, содержащие одинаковый набор атрибутов, должны обрабатываться одинаково.
Использование автонумерации в качестве первичного ключа является признаком отсутствия модели данных. После какого-либо сбоя такую базу данных, скорее всего, придется воссоздавать с нуля.
Вот так создают таблицы с данными о водителях неопытные новички:
CREATE Drivers
(driver_id AUTONUMBER NOT NULL PRIMARY KEY,
ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin));
В такую таблицу одну и ту же строку можно ввести хоть тысячу, хоть миллион раз. Ни о какой целостности данных и речи быть не может. А вот вариант с естественным ключом, основанным на номере социального страхования водителя и на номере шасси автомобиля:
CREATE Drivers
(ssn CHAR(9) NOT NULL REFERENCES Personnel(ssn),
vin CHAR(17) NOT NULL REFERENCES Motorpool(vin),
PRIMARY KEY (ssn, vin));
Другая проблема возникает, когда естественный ключ существует (а в корректной модели данных он есть обязательно). В этом случае столбцы можно обновлять как по ключу, так и по автоматическому номеру. Поскольку нет способа связать ключ с автономером, не будет и целостности данных.
Чтобы убедиться в этом, рассмотрим типичную для новичков схему. Я таких называю “id-иотами”, поскольку у них в таблицах обязательно есть столбец “id” с автонумерацией:
CREATE TABLE Personnel
(id AUTONUMBER NOT NULL PRIMARY KEY,-неправильный ключ
ssn CHAR(9) NOT NULL,-настоящий ключ
..);
INSERT INTO Personnel VALUES ('999999999*, ..);
Изменим строку в таблице Personnel, ориентируясь на значение столбца “id”:
UPDATE Personnel
SET ssn = '666666666' WHERE id = 1;
или на значение естественного ключа:
UPDATE Personnel
SET ssn = '666666666' WHERE ssn = '999999999';
Теперь удалим строку и создадим ее заново:
BEGIN ATOMIC
DELETE FROM Personnel WHERE id = 1;
INSERT INTO Personnel VALUES ('666666666', ..);
END;
Что случилось при этом с таблицами, которые ссылались на таблицу Personnel? Представьте себе таблицу с данными о футбольной команде компании, в которой также есть столбцы “id” и “ssn”. При изменении номера социального страхования необходимо каскадное DRI-изменение, но на основе столбца “id” я этого сделать не смогу и потому не имею представления, сколько разных значений SSN будет записано в БД для этого работника. Столбец “id” в лучшем случае не нужен, в худшем же попросту опасен.
Наконец, обратимся к авторитетам и процитируем Кодда (1979): “Есть три трудности, возникающие при идентификации элементов базы данных с помощью ключей, к которым имеет доступ пользователь.
1. Реальные значения пользовательских ключей определяются пользователями и могут ими изменяться. Допустим, при слиянии двух компаний объединяются две базы данных о работниках. В результате менять придется некоторые или все табельные номера работников.
2. В двух отношениях могут использоваться различные пользовательские ключи (например, один, основанный на номере социального страхования, и другой, основанный на табельном номере работника), хотя оба они указывают на один и тот же элемент данных.
3. Иногда возникает необходимость в поиске информации об элементе базы данных, которому значение пользовательского ключа еще неприсвоено или у которого оно уже отсутствует, например, о соискателе, который еще не принят на работу, или о сотруднике, ушедшем на пенсию.
Из этих трудностей вытекает важное следствие: объединение по общему ключу может дать результаты, отличные от объединения по общему элементу. Возможное решение состоит в организации доменов, содержащих назначенные системой суррогатные ключи. Пользователи БД могут заставить систему создать или удалить суррогатный ключ, но изменять его значение они не могут, да оно им и неизвестно. Это означает, что суррогатный ключ работает подобно индексу: он создается пользователем, но управляется системой и пользователю никогда не показывается. То есть, не используется в запросах, DRI — ни в чем, что делает пользователь”.
Исключения
Если таблица вам нужна просто для размещения данных или для еще какой-то цели, отличной от создания БД, тогда вы, конечно, вольны применять любые нестандартные возможности, лишь бы они удовлетворяли вашим целям. В первое время после появления реляционных БД мы сплошь да рядом этим занимались. Сегодня, впрочем, вам стоит подумать о возможном применении ETL или других подобных программных средств, которые появились за последние годы.
Файлы — не таблицы
Если попытаться выразить эту мысль максимально просто, речь идет о различии между физическим и логическим представлением данных, хотя на самом деле все сложнее. Файловая система — это просто собрание файлов, далеко не все из которых реально нужны. База данных — это единый блок информации, в котором целое предприятие представлено в виде таблиц, ограничений и пр.
Файлы независимы друг от друга, а таблицы базы данных взаимосвязаны. Вы открываете БД целиком, а не отдельные таблицы в ней, тогда как файлы открываются по отдельности. Действие в отношении одного файла не затрагивает другие файлы, таблицы же взаимодействуют друг с другом посредством DRI, процедур-триггеров и т.д.
Основополагающая идея БД — собрать информацию таким образом, чтобы избежать избыточного хранения данных во многих файлах и обеспечить независимость от языка программирования.
Файл состоит из записей, записи — из полей. Записи в файле упорядочены, доступ к ним может осуществляться по физическому расположению; в отношении таблицы это неверно. Говоря о файле, можно говорить “первая запись”, “последняя запись”, “следующие п записей”, в таблице же понятие первой и последней строки отсутствует.
Файл обычно привязан к определенному языку — попробуйте прочитать файл, созданный с помощью Фортрана, программой на Коболе. БД не привязана к конкретному языку; внутренние типы данных SQL преобразуются в типы данных хост-языка.
Поле существует лишь постольку, поскольку существует читающая его программа; столбец существует, поскольку он включен в таблицу базы данных. Столбец независим от хост-языка приложения, которое будет к нему обращаться.
В процедурном языке оператор “READ a, b, с FROM FileX;” не приведет к тому же результату, что оператор “READ b, с, a FROM FileX;”. Выполнив оператор “READ a, a, a FROM FileX;”, вы дважды перезапишете значение локальной переменной. В SQL оператор “SELECT a, b, с FROM TableX” вернет те же данные, что и оператор “SELECT b, с, a FROM TableX”, поскольку данные идентифицируются по имени, а не по положению.
Поле может иметь постоянную или переменную длину, тип данных поля может меняться (union в Си, VARIANT в Паскале, REDEFINES в Коболе, EQUIVALENCE в Фортране).
Столбец — это скалярное значение, извлеченное из единого домена (домен = тип данных + ограничения + отношения) и представляемое одним и только одним типом данных. Вы не обязаны иметь ни малейшего представления о внутреннем физическом представлении столбца, поскольку никогда с ним не сталкиваетесь.
Рассмотрим типы данных, связанные со временем: в SQL Server данные типа DATETIME (так в этом продукте назван тип TIMESTAMP) представляются двоичным числом (представление времени, принятое в UNIX), а в DB2 тип данных TIMESTAMP представляется строкой цифр (представление, принятое в Коболе). Но вы не должны об этом беспокоиться, заботясь о самих данных, а не об их физическом представлении.
У полей нет ограничений, нет отношений, нет типа данных; каждое приложение назначает эти параметры самостоятельно, и они необязательно совпадают! Отсутствие контроля за целостностью данных было одной из причин появления реляционных БД. У строк и столбцов есть ограничения. Записи и поля могут содержать все, что угодно, что зачастую и происходит! Поговорите об этом с любым программистом, который когда-либо занимался организацией большого массива данных. Мне особенно нравится, когда в электронной складской ведомости в поле для номера изделия я вижу слова “Ненавижу эту работу”.
Кодд (1979) определил строку как представление простого одиночного факта. Запись обычно представляет собой комбинацию множества фактов. Иными словами, размер файла не нормирован; вы просто запихиваете в него все новые данные в расчете на то, что соберете в нем все необходимое. Когда системе нужны новые данные, вы добавляете в конец записи новые поля. Так и возникают записи, размер которых исчисляется килобайтами.