07.04.2024 11:39
nvv 2 3300 Источник

Продолжим разбор проверок структуры базы данных, на примере PostgeSQL. Данная статья будет посвящена проверкам связанным с ограничением FOREIGN KEY (FK). Часть проверок целесообразно выполнять на регулярной основе, а некоторые позволяют лучше понять структуру проекта при первом знакомстве и применяются только один раз.

Список статей

Ограничение ссылочной целостности

Ограничение ссылочной целостности — гибкий и мощный инструмент контроля данных, предоставляемый СУБД [1]. Оставим за пределами статьи обсуждение вопросов вида использовать FK на стороне СУБД или реализовывать все ограничения в коде приложения.

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

Соответствие типов в ссылающейся и целевой таблицах

Типы колонок в ссылающемся и целевом отношении должны совпадать. Колонка с типом integer должна ссылаться на колонку с типом integer. Это исключает лишние конвертации на уровне СУБД и в коде приложения, снижает количество ошибок, которые могут появляться из-за несоответствия типов в будущем.

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

Задача: проверить соответствие типов колонок в ссылающихся и целевых таблицах.

Пример для проверки соответствия типов в ссылающейся и целевой таблице
-- колонка id в целевом отношении integer, в ссылающемся отношении 
-- колонка fk1001_2_id bigint

-- целевое отношение (справочник) 
DROP TABLE IF EXISTS public.fk1001_2 CASCADE;
CREATE TABLE public.fk1001_2
(
    id    integer GENERATED ALWAYS AS IDENTITY NOT NULL,
    value text NOT NULL,
    CONSTRAINT fk1001_2_pk PRIMARY KEY (id, value)
);

-- ссылающееся отношение
DROP TABLE IF EXISTS public.fk1001_2_fk;
CREATE TABLE public.fk1001_2_fk
(
    fk1001_2_id bigint NOT NULL,
    value text NOT NULL,
    CONSTRAINT fk1001_2_fk_fk1001_2 FOREIGN KEY (fk1001_2_id, value) 
        REFERENCES public.fk1001_2(id, value)
);

Код проверки соответствия типов в ссылающейся и целевой таблице
WITH
-- объединяем ограничения FK с атрибутами, которые в них задействованы
fk_with_attributes AS (
        SELECT
            c.conname as fk_name,
            c.conrelid,
            c.confrelid,
            fk_conkey.conkey_order AS att_order,
            fk_conkey.conkey_number,
            fk_confkey.confkey_number,
            rel_att.attname AS rel_att_name,
            rel_att.atttypid AS rel_att_type_id,
            rel_att.atttypmod AS rel_att_type_mod,
            rel_att.attnotnull AS rel_att_notnull,
            frel_att.attname AS frel_att_name,
            frel_att.atttypid AS frel_att_type_id,
            frel_att.atttypmod AS frel_att_type_mod,
            frel_att.attnotnull AS frel_att_notnull
        FROM pg_catalog.pg_constraint AS c
            CROSS JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS fk_conkey(conkey_number, conkey_order)
            LEFT JOIN LATERAL UNNEST(c.confkey) WITH ORDINALITY AS fk_confkey(confkey_number, confkey_order)
                ON fk_conkey.conkey_order = fk_confkey.confkey_order
            LEFT JOIN pg_catalog.pg_attribute AS rel_att
                ON rel_att.attrelid = c.conrelid AND rel_att.attnum = fk_conkey.conkey_number
            LEFT JOIN pg_catalog.pg_attribute AS frel_att
                ON frel_att.attrelid = c.confrelid AND frel_att.attnum = fk_confkey.confkey_number
        WHERE c.contype IN ('f')
    )
SELECT
    c.fk_name,       -- наименование ограничения fk
    r_from.relname,  -- ссылающееся отношение
    c.rel_att_names, -- атрибуты в ссылающемся отношении
    r_to.relname,    -- целесое отношение
    c.frel_att_names -- атрибуты в целевом отношении
FROM (
    -- отбираем FK, у которые есть расхождения типов колонок
    SELECT
        fk_name,
        conrelid,
        confrelid,
        array_agg (rel_att_name order by att_order ) as rel_att_names,
        array_agg (frel_att_name order by att_order ) as frel_att_names
    FROM fk_with_attributes
    WHERE
        ((rel_att_type_id <> frel_att_type_id) OR (rel_att_type_mod <> frel_att_type_mod))
    GROUP BY 1, 2, 3
) AS c
    INNER JOIN pg_catalog.pg_class AS r_from
        ON r_from.oid = c.conrelid
    INNER JOIN pg_catalog.pg_class AS r_to
        ON r_to.oid = c.confrelid

В ссылающейся таблице часть колонок FK могут принимать значения NULL

Значительная часть разработчиков полагает, что достаточно установить ограничение FK и база как-то сама всё проконтролирует. Сталкиваясь впоследствии данными, которые не соответствуют их ожиданиям, такие разработчики, не редко не пытаются разобраться в причинах, а отказываются от FK и других ограничений на стороне БД, полагая их сложными или не работающими.

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

Сравнение с NULL имеет особенности, которые следует знать и учитывать [2]. Для упрощения поведения FK с колонками, которые могут содержать NULL, есть уточняющие ключи - MATCH SIMPLE (используется по умолчанию) и MATCH FULL (MATCH PARTIAL в PostgreSQL не реализован) [3].

Задача: найти FK, у которые колонки в ссылающейся таблице могут принимать значение NULL, и при этом тип сравнения не установлен в MATCH FULL.

Пример для проверки случая, когда в ссылающейся таблице часть колонок FK могут принимать значения NULL
-- целевое отношение (справочник) 
DROP TABLE IF EXISTS public.fk1002_2 CASCADE;
CREATE TABLE public.fk1002_2
(
    id    integer NOT NULL,
    value varchar(10) NOT NULL,
    CONSTRAINT fk1002_2_pk PRIMARY KEY (id, value)
);

-- заполнение справочника
INSERT INTO public.fk1002_2 (id, value) VALUES (10, '10');
INSERT INTO public.fk1002_2 (id, value) VALUES (20, '20');

-- ссылающееся отношение
DROP TABLE IF EXISTS public.fk1002_2_fk;
CREATE TABLE public.fk1002_2_fk
(
    fk1002_2_id integer NOT NULL,
    fk1002_2_value varchar(10),
    CONSTRAINT fk1002_2_fk_fk1002_2 
      FOREIGN KEY (fk1002_2_id, fk1002_2_value) 
        REFERENCES public.fk1002_2 (id, value)
);

-- добавление данных
-- из-за того, что поле fk1002_2_value может содержать NULL
-- обе записи будут добавлены в таблицу
-- если бы для CONSTRAINT fk1002_2_fk_fk1002_2 установили MATCH FULL
-- тогда вторая запись не была бы добавлена в таблицу
INSERT INTO public.fk1002_2_fk (fk1002_2_id, fk1002_2_value) VALUES (20, '20');
INSERT INTO public.fk1002_2_fk (fk1002_2_id, fk1002_2_value) VALUES (30, NULL);

Код проверки для случая, когда в ссылающейся таблице часть колонок FK могут принимать значения NULL
WITH
-- объединяем ограничения FK с атрибутами, которые в них задействованы
fk_with_attributes AS (
        SELECT
            c.conname as fk_name,
            c.conrelid,
            c.confrelid,
            c.confmatchtype,
            fk_conkey.conkey_order AS att_order,
            fk_conkey.conkey_number,
            fk_confkey.confkey_number,
            rel_att.attname AS rel_att_name,
            rel_att.atttypid AS rel_att_type_id,
            rel_att.atttypmod AS rel_att_type_mod,
            rel_att.attnotnull AS rel_att_notnull,
            frel_att.attname AS frel_att_name,
            frel_att.atttypid AS frel_att_type_id,
            frel_att.atttypmod AS frel_att_type_mod,
            frel_att.attnotnull AS frel_att_notnull
        FROM pg_catalog.pg_constraint AS c
            CROSS JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS fk_conkey(conkey_number, conkey_order)
            LEFT JOIN LATERAL UNNEST(c.confkey) WITH ORDINALITY AS fk_confkey(confkey_number, confkey_order)
                ON fk_conkey.conkey_order = fk_confkey.confkey_order
            LEFT JOIN pg_catalog.pg_attribute AS rel_att
                ON rel_att.attrelid = c.conrelid AND rel_att.attnum = fk_conkey.conkey_number
            LEFT JOIN pg_catalog.pg_attribute AS frel_att
                ON frel_att.attrelid = c.confrelid AND frel_att.attnum = fk_confkey.confkey_number
        WHERE c.contype IN ('f')
    )
SELECT
    c.fk_name,       -- наименование ограничения fk
    r_from.relname,  -- ссылающееся отношение
    c.rel_att_names  -- nullable атрибуты в ссылающемся отношении
FROM (
    -- отбираем FK, у которые есть расхождения типов колонок
    SELECT
        fk_name,
        conrelid,
        confrelid,
        array_agg (rel_att_name order by att_order ) as rel_att_names
    FROM fk_with_attributes
    WHERE
        (rel_att_notnull IS NOT TRUE)
        AND confmatchtype NOT IN ('f')
    GROUP BY 1, 2, 3
) AS c
    INNER JOIN pg_catalog.pg_class AS r_from
        ON r_from.oid = c.conrelid

FK возможно совпадают или устарели

Ситуация, когда ссылающаяся таблица имеет несколько FK на целевую таблицу, достаточно распространена. Например, таблица аналогов товаров может содержать две ссылки на товары, которые являются аналогами друг друга, и дополнительные служебные поля.

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

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

Задача: найти пары FK, которые имеют одинаковые ссылающуюся и целевую таблицы, при этом атрибуты в ссылающейся таблице у них совпадают или пересекаются.

Пример для проверки случая, когда FK возможно совпадают или устарели
DROP TABLE IF EXISTS public.fk1010_1 CASCADE;
CREATE TABLE public.fk1010_1
(
    id    integer NOT NULL,
    i1    integer NOT NULL,
    i2    integer NOT NULL,
    CONSTRAINT fk1010_1_pk PRIMARY KEY (id),
    CONSTRAINT fk1010_1_unique_id_i1 UNIQUE (id, i1),
    CONSTRAINT fk1010_1_unique_id_i2 UNIQUE (id, i2),
    CONSTRAINT fk1010_1_unique_i2 UNIQUE (i2)
);

DROP TABLE IF EXISTS public.fk1010_1_fk;
CREATE TABLE public.fk1010_1_fk
(
    id    integer GENERATED ALWAYS AS IDENTITY NOT NULL,
    fk1010_1_id integer NOT NULL,
    fk1010_1_i1 integer NOT NULL,
    fk1010_1_i2 integer NOT NULL,
    CONSTRAINT fk1010_1_fk_pk PRIMARY KEY (id),
    CONSTRAINT fk1010_1_fk_fk1010_1_id FOREIGN KEY (fk1010_1_id) REFERENCES public.fk1010_1(id),
    CONSTRAINT fk1010_1_fk_fk1010_1_id_i1 FOREIGN KEY (fk1010_1_id, fk1010_1_i1) REFERENCES public.fk1010_1(id, i1),
    CONSTRAINT fk1010_1_fk_fk1010_1_id_i2 FOREIGN KEY (fk1010_1_id, fk1010_1_i2) REFERENCES public.fk1010_1(id, i2),
    CONSTRAINT fk1010_1_fk_fk1010_1_id_i2_copy FOREIGN KEY (fk1010_1_id, fk1010_1_i2) REFERENCES public.fk1010_1(id, i2),
    CONSTRAINT fk1010_1_fk_fk1010_1_i2 FOREIGN KEY (fk1010_1_i2) REFERENCES public.fk1010_1(i2)
);

-- FK  у которых совпадают атрибуты
-- fk1010_1_fk_fk1010_1_id_i2 и fk1010_1_fk_fk1010_1_id_i2_copy

-- FK имеют пересечения по атрибутам
-- fk1010_1_fk_fk1010_1_id и fk1010_1_fk_fk1010_1_id_i1
-- fk1010_1_fk_fk1010_1_id и fk1010_1_fk_fk1010_1_id_i2
-- fk1010_1_fk_fk1010_1_id и fk1010_1_fk_fk1010_1_id_i2_copy
-- fk1010_1_fk_fk1010_1_id_i1 и fk1010_1_fk_fk1010_1_id_i2
-- fk1010_1_fk_fk1010_1_id_i1 и fk1010_1_fk_fk1010_1_id_i2_copy
-- fk1010_1_fk_fk1010_1_i2 и fk1010_1_fk_fk1010_1_id_i2
-- fk1010_1_fk_fk1010_1_i2 и fk1010_1_fk_fk1010_1_id_i2_copy

Код для проверки случая, когда FK возможно совпадают
WITH
-- объединяем ограничения FK с атрибутами, которые в них задействованы
fk_with_attributes AS (
        SELECT
            c.conname as fk_name,
            c.conrelid,
            c.confrelid,
            fk_conkey.conkey_order AS att_order,
            fk_conkey.conkey_number,
            fk_confkey.confkey_number,
            rel_att.attname AS rel_att_name,
            rel_att.atttypid AS rel_att_type_id,
            rel_att.atttypmod AS rel_att_type_mod,
            rel_att.attnotnull AS rel_att_notnull,
            frel_att.attname AS frel_att_name,
            frel_att.atttypid AS frel_att_type_id,
            frel_att.atttypmod AS frel_att_type_mod,
            frel_att.attnotnull AS frel_att_notnull
        FROM pg_catalog.pg_constraint AS c
            CROSS JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS fk_conkey(conkey_number, conkey_order)
            LEFT JOIN LATERAL UNNEST(c.confkey) WITH ORDINALITY AS fk_confkey(confkey_number, confkey_order)
                ON fk_conkey.conkey_order = fk_confkey.confkey_order
            LEFT JOIN pg_catalog.pg_attribute AS rel_att
                ON rel_att.attrelid = c.conrelid AND rel_att.attnum = fk_conkey.conkey_number
            LEFT JOIN pg_catalog.pg_attribute AS frel_att
                ON frel_att.attrelid = c.confrelid AND frel_att.attnum = fk_confkey.confkey_number
        WHERE c.contype IN ('f')
    ),
    --
    fk_with_attributes_grouped AS (
        SELECT
            fk_name,
            conrelid,
            confrelid,
            array_agg (rel_att_name order by att_order) as rel_att_names,
            array_agg (frel_att_name order by att_order) as frel_att_names
        FROM fk_with_attributes
        GROUP BY 1, 2, 3
    )
SELECT
    r_from.relname,  -- ссылающееся отношение
    c1.fk_name,      -- наименование ограничения fk
    c2.fk_name       -- наименование ограничения fk (потенцильный дубль)
FROM fk_with_attributes_grouped AS c1
    INNER JOIN fk_with_attributes_grouped AS c2 ON c1.fk_name < c2.fk_name 
        AND c1.conrelid = c2.conrelid AND c1.confrelid = c2.confrelid
        AND c1.rel_att_names = c2.rel_att_names
    INNER JOIN pg_catalog.pg_class AS r_from ON r_from.oid = c1.conrelid

Код для проверки случая, когда FK имеют пересечения по атрибутам
WITH
-- объединяем ограничения FK с атрибутами, которые в них задействованы
fk_with_attributes AS (
        SELECT
            c.conname as fk_name,
            c.conrelid,
            c.confrelid,
            fk_conkey.conkey_order AS att_order,
            fk_conkey.conkey_number,
            fk_confkey.confkey_number,
            rel_att.attname AS rel_att_name,
            rel_att.atttypid AS rel_att_type_id,
            rel_att.atttypmod AS rel_att_type_mod,
            rel_att.attnotnull AS rel_att_notnull,
            frel_att.attname AS frel_att_name,
            frel_att.atttypid AS frel_att_type_id,
            frel_att.atttypmod AS frel_att_type_mod,
            frel_att.attnotnull AS frel_att_notnull
        FROM pg_catalog.pg_constraint AS c
            CROSS JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS fk_conkey(conkey_number, conkey_order)
            LEFT JOIN LATERAL UNNEST(c.confkey) WITH ORDINALITY AS fk_confkey(confkey_number, confkey_order)
                ON fk_conkey.conkey_order = fk_confkey.confkey_order
            LEFT JOIN pg_catalog.pg_attribute AS rel_att
                ON rel_att.attrelid = c.conrelid AND rel_att.attnum = fk_conkey.conkey_number
            LEFT JOIN pg_catalog.pg_attribute AS frel_att
                ON frel_att.attrelid = c.confrelid AND frel_att.attnum = fk_confkey.confkey_number
        WHERE c.contype IN ('f')
    ),
    --
    fk_with_attributes_grouped AS (
        SELECT
            fk_name,
            conrelid,
            confrelid,
            array_agg (rel_att_name order by att_order) as rel_att_names,
            array_agg (frel_att_name order by att_order) as frel_att_names
        FROM fk_with_attributes
        GROUP BY 1, 2, 3
    )
SELECT
    r_from.relname,  -- ссылающееся отношение
    c1.fk_name,      -- наименование ограничения fk
    c2.fk_name       -- наименование ограничения fk (пересечение по атрибутам)
FROM fk_with_attributes_grouped AS c1
    INNER JOIN fk_with_attributes_grouped AS c2 ON c1.fk_name < c2.fk_name 
        AND c1.conrelid = c2.conrelid AND c1.confrelid = c2.confrelid
        AND (c1.rel_att_names && c2.rel_att_names)
    INNER JOIN pg_catalog.pg_class AS r_from ON r_from.oid = c1.conrelid

Отношения не участвующие в FK

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

  • служебные таблицы, не являющиеся частью проекта (таблицы системы миграции типа liquibase, структуры для отдельных плагинов или сторонних модулей);

  • таблицы, которые уже не используются или созданы ошибочно, но не были своевременно удалены (опечатки в названиях, отголоски устаревшего функционала);

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

Задача: найти таблицы, которые не участвуют в FK.

Код для поиска таблиц не участвующих в FK
SELECT
	n.nspname,  -- схема
	t.relname   -- отношение
FROM pg_catalog.pg_class AS t
    INNER JOIN pg_catalog.pg_namespace AS n 
        ON t.relnamespace = n.oid
WHERE
    relkind IN ('r', 'p')
    AND t.oid NOT IN (SELECT conrelid FROM pg_catalog.pg_constraint WHERE contype IN ('f'))
    AND t.oid NOT IN (SELECT confrelid FROM pg_catalog.pg_constraint WHERE contype IN ('f'))
    AND n.nspname NOT IN ('information_schema', 'pg_catalog') 
    AND n.nspname NOT LIKE 'pg_toast%'

Ссылки

[1] https://postgrespro.ru/docs FK

[2] https://postgrespo.ru/docs functions comparison

[3] https://postgrespro.ru/docs CREATE TABLE (см. по поиску MATCH FULL)

Рассмотренные выше проверки можно найти в репозитории https://github.com/sdblist/db_verifier

Комментарии (2)


  1. Serg7777
    07.04.2024 13:16
    #26697559

    Не понял, руками подставлять все fk в sql-выражение? Это хорошо когдаpublic.fk1010_1 один в бд, а у меня их много.


    1. nvv Автор
      07.04.2024 13:16
      #26697571

      В статье приведён запрос, который выберет и выведет все FK, у которых найдены признаки потенциальной проблемы. Посмотрите в блоке "Код проверки", выполните его.