всем привет
вопрос к товарищу Мелкому
если я создал внешний ключ по полю например foreign_id_fk, и уже существует индекс foreign_id, other_column_id, other_column2_id
будет ли он использован или новый создаст? как проверить? как заставить?
1. LIME - 02 Сентября, 2017 - 15:37:01 - перейти к сообщению
2. Мелкий - 02 Сентября, 2017 - 17:22:46 - перейти к сообщению
Создание FK требует уже созданный уникальный ключ на таблице на которую ссылаемся. Если уникальный ключ по трём полям, а повесить FK хотим не на все 3 - то получим отлуп. Это вот здесь (кстати, deferrable ограничения для fk использовать тоже нельзя)
Для таблицы, которая будет содержать ссылку - индекс навешиванием FK вообще не создаётся никакой и нигде, и его существование не проверяется никак. Что иногда бывает сюрпризом в продакшене.
Для таблицы, которая будет содержать ссылку - индекс навешиванием FK вообще не создаётся никакой и нигде, и его существование не проверяется никак. Что иногда бывает сюрпризом в продакшене.
Спойлер (Отобразить)
CODE (sql):
скопировать код в буфер обмена
скопировать код в буфер обмена
- melkij=> CREATE TABLE origintable (id serial PRIMARY KEY);
- CREATE TABLE
- melkij=> CREATE TABLE reftable (ref_id int);
- CREATE TABLE
- melkij=> \d origintable
- Таблица "public.origintable"
- Столбец | Тип | Модификаторы
- ---------+---------+----------------------------------------------------------
- id | integer | NOT NULL DEFAULT NEXTVAL('origintable_id_seq'::regclass)
- Индексы:
- "origintable_pkey" PRIMARY KEY, btree (id)
- melkij=> \d reftable
- Таблица "public.reftable"
- Столбец | Тип | Модификаторы
- ---------+---------+--------------
- ref_id | integer |
- melkij=> ALTER TABLE reftable ADD constraint foreign_id_fk FOREIGN KEY (ref_id) REFERENCES origintable (id);
- ALTER TABLE
- melkij=> \d reftable
- Таблица "public.reftable"
- Столбец | Тип | Модификаторы
- ---------+---------+--------------
- ref_id | integer |
- Ограничения внешнего ключа:
- "foreign_id_fk" FOREIGN KEY (ref_id) REFERENCES origintable(id)
- melkij=> CREATE TABLE origintable2 (id1 int, id2 int);
- CREATE TABLE
- melkij=> CREATE UNIQUE INDEX ON origintable2 USING btree(id1,id2);
- CREATE INDEX
- melkij=> ALTER TABLE reftable ADD constraint foreign_id_fk2 FOREIGN KEY (ref_id) REFERENCES origintable2 (id1);
- ОШИБКА: в целевой внешней таблице "origintable2" нет ограничения уникальности, соответствующего данным ключам
- melkij=> INSERT INTO origintable SELECT generate_series(2,50000);
- INSERT 0 49999
- melkij=> INSERT INTO reftable SELECT generate_series(2,50000);
- INSERT 0 49999
- melkij=> analyze ;
- ANALYZE
- melkij=> EXPLAIN analyze SELECT * FROM reftable WHERE ref_id = 43643;
- QUERY PLAN
- ----------------------------------------------------------------------------------------------------
- Seq Scan ON reftable (cost=0.00..847.00 rows=1 width=4) (actual time=4.104..4.680 rows=1 loops=1)
- Filter: (ref_id = 43643)
- Rows Removed BY Filter: 49999
- Planning time: 0.066 ms
- Execution time: 4.694 ms
- (5 строк)
- melkij=> DELETE FROM origintable WHERE id = 43643;
- ОШИБКА: UPDATE или DELETE в таблице "origintable" нарушает ограничение внешнего ключа "foreign_id_fk" таблицы "reftable"
- ПОДРОБНОСТИ: На ключ (id)=(43643) всё ещё есть ссылки в таблице "reftable".
- melkij=> INSERT INTO origintable SELECT 55555;
- INSERT 0 1
- melkij=> EXPLAIN (analyze) DELETE FROM origintable WHERE id = 55555;
- QUERY PLAN
- ------------------------------------------------------------------------------------------------------------------------------------
- DELETE ON origintable (cost=0.29..8.31 rows=1 width=6) (actual time=0.031..0.031 rows=0 loops=1)
- -> INDEX Scan USING origintable_pkey ON origintable (cost=0.29..8.31 rows=1 width=6) (actual time=0.016..0.017 rows=1 loops=1)
- INDEX Cond: (id = 55555)
- Planning time: 0.064 ms
- TRIGGER FOR constraint foreign_id_fk: time=4.976 calls=1
- Execution time: 5.029 ms
- (6 строк)
- -- внимание на время проверки ограничения. Да, там seqscan.
- melkij=> INSERT INTO origintable SELECT 55555;
- INSERT 0 1
- melkij=> CREATE INDEX ON reftable USING btree(ref_id );
- CREATE INDEX
- melkij=> EXPLAIN (analyze) DELETE FROM origintable WHERE id = 55555;
- QUERY PLAN
- ------------------------------------------------------------------------------------------------------------------------------------
- DELETE ON origintable (cost=0.29..8.31 rows=1 width=6) (actual time=0.030..0.030 rows=0 loops=1)
- -> INDEX Scan USING origintable_pkey ON origintable (cost=0.29..8.31 rows=1 width=6) (actual time=0.016..0.016 rows=1 loops=1)
- INDEX Cond: (id = 55555)
- Planning time: 0.063 ms
- TRIGGER FOR constraint foreign_id_fk: time=0.324 calls=1
- Execution time: 0.378 ms
- (6 строк)
- -- а теперь куда более быстрый indexscan потому что появился подходящий индекс для проверки