Comments 30
Как правило, у любой таблицы есть первичный ключ (Primary Key, PK), и он необходим, чтобы уникально идентифицировать любую из строк этой таблицы.
К сожалению, практически все забывают добавлять к этой фразе одно небольшое, но имхо крайне существенное дополнение.
Первичный ключ позволяет однозначно идентифицировать запись в течение всего срока жизни таблицы.
Да, для правильного понимания это дополнение - крайне важно! Запись создана - и в её составе (либо для неё в случае автогенерации) присутствует уникальное выражение первичного ключа. Запись удалили, но тем не менее это уникальное выражение - присутствует! Например, запрос по нему позволяет убедиться, что запись отсутствует/удалена.
Непонимание этой в общем простой вещи приводит к появлению странных для опытного пользователя вопросов "А как убрать образовавшиеся вследствие удаления дырки в поле автоинкремента?". И правильный ответ "Никак, более того, это крайне вредное мероприятие!" порой с трудом достукивается до сознания. Только после того, как проговоришь вышенаписанное, да ещё добавишь, что каждый должен заниматься своим делом, вот ПК-автоинкремент уникально идентифицирует записи, а если нужна непрерывная нумерация, то это другая задача, и её должно решать другое, дополнительно создаваемое под эту задачу поле, человек начинает более-менее понимать суть своей ошибки. Да и то не всегда.
Вообще, если говорить о поле-синтетическом первичном ключе, то я стараюсь внедрить в собеседника следующую мысль: в нормальных условиях никто и никогда не должен даже смотреть на это поле. Оно - не для пользователя. Оно в первую очередь для сервера, для правильной работы его подсистемы контроля целостности и непротиворечивости данных, и в первую голову для обеспечения правильной работы внешних ключей.
Да, из этого правила, как и из любого другого, бывают исключения. Но они весьма редки. И для того, чтобы отойти от вышеописанного, нужно иметь достаточно серьёзное обоснование.
чтобы что-то создать в SQL, нам необходима команда
CREATE- она отвечает за создание в SQL любого типа объектов
Ну это не совсем так. Есть и иные команды, которые создают объекты БД. Например, таблица может быть создана запросом SELECT INTO.
а если нужна непрерывная нумерация, то это другая задача, и её должно решать другое, дополнительно создаваемое под эту задачу поле
Разделяю всю боль от разработки бухгалтерского софта . ))
таблица может быть создана запросом SELECT INTO
Думаю, начинающему разработчику SELECT INTO использовать придется очень нескоро, поэтому множество вещей в курсе несколько упрощено.
Если бы PostgreSQL умел использовать параллельные планы запросов для INSERT INTO, тогда другое дело. А пока, если хочешь вставку с параллельным планом - используй или CREATE TABLE ... AS, SELECT ... INTO.
В этом смысле CREATE TABLE AS выглядит куда логичнее, позволяя определить дополнительные опции для таблицы (филфактор, таблспейс, COMMIT-поведение). А SELECT INTO - это для использования в функциях больше подходит, для помещения результата в переменную.
Суть не в этом, а в утверждении, что
начинающему разработчику
SELECT INTOиспользовать придется очень нескоро
Как только этому разработчику захочется параллельного плана - уже потребуется. А CREATE TABLE AS и SELECT INTO часто взаимозаменяемы.
Документация говорит, что у SELECT INTO нет преимуществ, так зачем использовать вместо CREATE TABLE AS?
Команда
SELECT INTOдействует подобноCREATE TABLE AS, но рекомендуется использоватьCREATE TABLE AS, так какSELECT INTOне поддерживается в ECPG и PL/pgSQL вследствие того, что они воспринимают предложениеINTOпо-своему. К тому же,CREATE TABLE ASпредоставляет больший набор возможностей, чемSELECT INTO.В отличие от
CREATE TABLE AS, командаSELECT INTOне позволяет задать свойства таблицы, например выбрать метод доступа с помощью указанияUSING методили табличное пространство с помощьюTABLESPACE табл_пространство. Если это требуется, используйте командуCREATE TABLE AS. Таким образом, для новой таблицы выбирается метод доступа к таблицам по умолчанию.
Например, затем, что SELECT INTO поддерживается MS SQL, а CREATE TABLE AS - нет. И если приложение должно работать и там и там, CREATE TABLE AS лучше избегать.
Поэтому, если у вас стоит задача писать максимально переносимый между СУБД софт, который будет все запросы формировать одинаково понятными для всех СУБД, то либо это будет очень сложным процессом, либо вы получите крайне неэффективные запросы, не использующие хоть какую-то специфику возможностей конкретной базы. То есть любой универсальный запрос на SQL будет одинаково выполняться на всех таких базах, но на всех - не настолько эффективно, насколько можно было бы сделать с учетом специфики.
Предпочтительно избегать необходимости писать кросс-СУБД-код. Лучше уж несколько версий специфичных запросов.
Увы, это решает бизнес. У меня сейчас идет ряд проектов, где требование переносимости между PostgreSQL и MS SQL было явно заявлено заказчиком. Нравится мне это, или нет.
Естественно, некоторые алгоритмы для достижения высокой производительности приходится выносить в хранимые процедуры/функции, что требует раздельной их поддержки для T-SQL и plpgsql, но стараемся минимизировать их количество. А вот в коде на C# поддержка нескольких версий специфичных запросов намного более трудоемка, чем на уровне хранимых процедур/функций, вызываемых из C# одинаково.
Запись удалили, но тем не менее это уникальное выражение - присутствует! Например, запрос по нему позволяет убедиться, что запись отсутствует/удалена.
Вот это утверждение не вполне корректно, потому что я могу удалить запись с каким-то значением PK, а затем вставить абсолютно другую, но с тем же значением:
CREATE TABLE _tmp(
x integer
, y integer
, PRIMARY KEY(x)
);
INSERT INTO _tmp(x, y) VALUES(1, 1);
DELETE FROM _tmp WHERE x = 1;
INSERT INTO _tmp(x, y) VALUES(1, 2);PK гарантирует уникальную идентификацию только для одновременно существующих в таблице записей.
Вот это именно то, чего при правильном проектировании быть не должно. Потому что с точки зрения истории получается существование двух различных записей с одним и тем же значением первичного ключа, пусть и в разные моменты времени - ну и какая тогда уникальность и однозначная идентификация?
Но ведь никто и не обещал, что PK гарантирует уникальность "во времени", а не "в моменте". А вопросы проектирования БД - тема для отдельного полноценного курса.
PK гарантирует уникальную идентификацию записи. О времени не говорится вообще ничего. Формально это верно - да, удалённое, но ранее существовавшее значение первичного ключа МОЖЕТ быть использовано в новой записи. Но исходя из его смысла - оно НЕ ДОЛЖНО использоваться.
Да, есть исключения. Более того - порой они имеют под собой достаточно серьёзное, а иногда даже и железобетонное, обоснование.. но, как правило, существование этого обоснования диктуется именно особенностью предметной области. И соответственно это всегда натуральные первичные ключи. В случаях же синтетических ключей такое обоснование и придумать-то сложно, и уж тем более для синтетических ключей такого обоснования не существует естественным образом. Кроме странного желания "сэкономить на спичках".
Как правило, у любой таблицы есть первичный ключ (Primary Key, PK), и он необходим, чтобы уникально идентифицировать любую из строк этой таблицы.
По стандарту это действительно так. Но в PostgreSQL первичный ключ несёт декоративные функции, выделяя один из уникальных индексов. Например, как индекс по умолчанию для внешних ключей. При наличии хотя бы одного уникального индекса в таблице, я, естественно, рекомендую один из них установить первичным в декоративных целях. Но, к сожалению, однозначно строку таблицы в PostgreSQL первичный ключ идентифицирует только при отсутствии в нем NULL полей или явном указании NULLS NOT DISTINCT.
Иными словами, ограничение (constraint) первичного ключа (primary key) в PostgreSQL не всегда гарантирует уникальную идентификацию записи в таблице. И об следует помнить.
выделяя один из уникальных индексов
Точнее, один из покрывающих уникальных индексов.
к сожалению, однозначно строку таблицы в PostgreSQL первичный ключ идентифицирует только при отсутствии в нем NULL полей
Включение столбца в PK как раз и накладывает на него NOT NULL:
CREATE TABLE _tmp(
x integer
, PRIMARY KEY(x)
);
INSERT INTO _tmp(x) VALUES(NULL);
-- ERROR: null value in column "x" of relation "_tmp" violates not-null constraint
-- DETAIL: Failing row contains (null).
Слово "отношения" здесь ключевое, поскольку именно оно определяет, что ваша СУБД является реляционной - то есть в такой базе будут находиться связанные таблицы.
База реляционная не из-за связей (relationship) между таблицами, а из-за реляционной модели хранения в таблицах (https://en.wikipedia.org/wiki/Relational_model). Между таблицами может не быть никаких связей, но база будет реляционной. Сколько можно эту чушь про отношения из курса в курс таскать?
Читаем по этой же ссылке:
Реляционная модель данных включает следующие компоненты:
Структурный аспект (составляющая) — данные в базе данных представляют собой набор отношений.
Наличие связей - это потенция, а вовсе даже не обязанность. СУБД реляционна не потому, что меж таблиц установлены связи, а потому, что они могут быть установлены - для этого в составе СУБД есть весь необходимый инструментарий, позволяющий такие связи установить, на их основе контролировать целостность и непротиворечивость, и блокировать действия, которые её нарушают.
В конце концов БД из одной таблицы - там и модели-то реляционной толком нет. И тем не менее...
Дата и время в PostgreSQL, технически, хранятся как целочисленные, со значением от Unix Epoch (01.01.1970) в соответствующих единицах (микросекундах или сутках):
В исходниках указано от 01.01.2000:
#define POSTGRES_EPOCH_JDATE 2451545 /* == date2j(2000, 1, 1) */А строкой выше:
#define UNIX_EPOCH_JDATE 2440588 /* == date2j(1970, 1, 1) */И пример с преобразованием timestamp туда-обратно это подтверждает.
Не подтверждает:
CREATE TABLE tmp_tmp AS
SELECT v.id, v.t
FROM ( VALUES
(1, '2000-01-01'::timestamp),
(2, '1970-01-01'::timestamp) ) v(id, t);
SELECT encode(t_data,'hex')
FROM heap_page_items(get_raw_page('tmp_tmp', 0));
01000000000000000000000000000000
02000000000000000020c8c4fea2fcffЯвно видим, что в странице дата 01.01.2000 лежит в виде нулевого значения, а 01.01.1970 - нет.
Убедили, да, слово "хранится" я использовал тут зря (сам ведь писал для нашего коллектора оптимизацию бинарного COPY-формата) - подправил.
вот за что мне нравятся статьи на Хабре, так это за комментарии!
Автору благодарность, что сам написал и расшевелил других!
Спасибо автору за понятную и нужную статью! Сам я нахватался знаний по реляционным БД на всяких курсах, остальному научился на практике и у более опытных товарищей. Но мои знания достаточно поверхностные и не структурированные, как раз искал что-то подобное чтобы разложить всё по полочкам и углубиться в теорию. Надеюсь, что автор продолжит в том же духе, буду с нетерпением ждать новых статей
Видеозапись всего курса можно посмотреть уже сейчас, не дожидаясь публикации транскриптов остальных лекций.
Курс «PostgreSQL для начинающих»: #1 — Основы SQL