SQLRU.net - RDF-канал
type: channel
description: Разработка приложений баз данных
items: Как построить FK на PK?
items: Re: Как построить FK на PK?

Как построить FK на PK?
type: item
description: CREATE TABLE MASTER ( ID BIGINT NOT NULL ); ALTER TABLE MASTER ADD CONSTRAINT PK_MASTER PRIMARY KEY (ID); CREATE TABLE SLAVE ( ID BIGINT NOT NULL, ADDITION VARCHAR(100) ); ALTER TABLE SLAVE ADD CONSTRAINT PK_SLAVE PRIMARY KEY (ID); ALTER TABLE SLAVE ADD CONSTRAINT FK_SLAVE FOREIGN KEY (ID) REFERENCES MASTER (ID); В результате имеем 2 идентичных по содержанию индекса PK_SLAVE и FK_SLAVE, что выглядит напрасной тратой ресурсов. Попытка ALTER TABLE SLAVE ADD CONSTRAINT FK_SLAVE FOREIGN KEY (ID) REFERENCES MASTER(ID) USING INDEX PK_SLAVE; приводит к ALTER TABLE SLAVE failed. Index PK_SLAVE already exists. Возможно, в случае, когда индекс FK_SLAVE таки вроде бы создался, он на самом деле не создается, а неявно используется PK_SLAVE, это было бы красиво и утешительно. Но возможно, что и нет. Как оно на самом деле, и как обойтись одним индексом?
creator: shalamyansky
date: 2023-10-23T15:55:39-00:00

Re: Как построить FK на PK?
type: item
description: Невозможно. Никак не обойтись.
creator: sim_84
date: 2023-10-23T16:43:32-00:00

Re: Как построить FK на PK?
type: item
description: констрейнта 2, значит и индекса 2. так устроен движок IB/FB. а вот Оракел для констрейнта индекс по умолчанию не создаёт. вследствие чего возможны забавные маланские мансы.
creator: МП
date: 2023-10-24T09:27:16-00:00

Re: Как построить FK на PK?
type: item
description: МП писал(а) Tue, 24 October 2023 12:27 констрейнта 2, значит и индекса 2. так устроен движок IB/FB. а вот Оракел для констрейнта индекс по умолчанию не создаёт. вследствие чего возможны забавные маланские мансы. Какие ? А то есть мнение что этот механизм неплохо бы и себе перенять.
creator: hvlad
date: 2023-10-24T10:37:50-00:00

Re: Как построить FK на PK?
type: item
description: hvlad писал(а) Tue, 24 October 2023 13:37 МП писал(а) Tue, 24 October 2023 12:27 констрейнта 2, значит и индекса 2. так устроен движок IB/FB. а вот Оракел для констрейнта индекс по умолчанию не создаёт. вследствие чего возможны забавные маланские мансы. Какие ? А то есть мнение что этот механизм неплохо бы и себе перенять. дык, ты ж мне отвечал на пост в пятнице: https://www.sqlru.net/index.php/mv/msg/89/2059/#msg_2059
creator: МП
date: 2023-10-24T10:54:12-00:00

Re: Как построить FK на PK?
type: item
description: МП писал(а) Tue, 24 October 2023 13:54 hvlad писал(а) Tue, 24 October 2023 13:37 МП писал(а) Tue, 24 October 2023 12:27 констрейнта 2, значит и индекса 2. так устроен движок IB/FB. а вот Оракел для констрейнта индекс по умолчанию не создаёт. вследствие чего возможны забавные маланские мансы. Какие ? А то есть мнение что этот механизм неплохо бы и себе перенять. дык, ты ж мне отвечал на пост в пятнице: https://www.sqlru.net/index.php/mv/msg/89/2059/#msg_2059 Т.е. кроме блокирования таблицы (знаем, знаем) других проблем не имеется ?
creator: hvlad
date: 2023-10-24T11:21:56-00:00

Re: Как построить FK на PK?
type: item
description: hvlad Т.е. кроме блокирования таблицы (знаем, знаем) других проблем не имеется ? это надо ораклоидов поспрошать. а я не настоящий сварщик. ©
creator: МП
date: 2023-10-24T11:32:23-00:00

Re: Как построить FK на PK?
type: item
description: А если (см. топик) вообще не создавать первичный ключ? У меня вполне прошло CREATE TABLE SLAVE ( ID BIGINT NOT NULL, ADDITION VARCHAR(100) ); ALTER TABLE SLAVE ADD CONSTRAINT FK_SLAVE FOREIGN KEY (ID) REFERENCES MASTER (ID); select * from SLAVE where ID = :ID ---- PLAN (S INDEX (FK_SLAVE)) То есть, если нет PK, то, когда необходимо, подхватится FK. Все вроде бы хорошо, но индекс FK_SLAVE создается неуникальным, как хотелось бы. Но если уникальность поддерживать на логическом уровне, то вроде должно работать, нет?
creator: shalamyansky
date: 2023-10-24T12:01:56-00:00

Re: Как построить FK на PK?
type: item
description: shalamyansky То есть, если нет PK, то, когда необходимо, подхватится FK. Все вроде бы хорошо, но индекс FK_SLAVE создается неуникальным, как хотелось бы. Но если уникальность поддерживать на логическом уровне, то вроде должно работать, нет? движок IB/FB умеет поддерживать уникальность только посредством соответствующего индекса. нет UNIQUE INDEX -- нет уникальности. зы: у Оракела есть возможность "пристегнуть" существующий индекс к создаваемому CONSTRAINT при помощи предиката USING INDEX. у нас же, USING INDEX позволяет только задать имя создаваемого индекса для создаваемого CONSTRAINT.
creator: МП
date: 2023-10-24T12:29:26-00:00

Re: Как построить FK на PK?
type: item
description: Это я понял, спасибо. И отсутствие поддержки уникальности на уровне полученного индекса некритично, хотя и неудобно. Теперь в ходе открывшихся обстоятельств вопрос звучит так: можно ли существовать без первичного ключа? Если да, то создание FK без PK вроде бы решает задачу создания единственного индекса вместо двух.
creator: shalamyansky
date: 2023-10-24T12:52:35-00:00

Re: Как построить FK на PK?
type: item
description: P.S. Для поддержки уникальности на "логическом" уровне можно перед каждой вставкой проверять на существование, это не особо накладно. Показать скрытый текст if( not exist( select ID from SLAVE where ID = :ID ) )then begin insert into SLAVE ( ID ) values ( :ID ) ; end
creator: shalamyansky
date: 2023-10-24T13:03:37-00:00

Re: Как построить FK на PK?
type: item
description: МП писал(а) Tue, 24 October 2023 15:29 зы: у Оракела есть возможность "пристегнуть" существующий индекс к создаваемому CONSTRAINT при помощи предиката USING INDEX. у нас же, USING INDEX позволяет только задать имя создаваемого индекса для создаваемого CONSTRAINT. И это тоже мы хотим перенять. И может быть кое-что ещё.
creator: hvlad
date: 2023-10-24T13:40:33-00:00

Re: Как построить FK на PK?
type: item
description: shalamyansky P.S. Для поддержки уникальности на "логическом" уровне можно перед каждой вставкой проверять на существование, это не особо накладно. касаемо "не особо накладно", ты ошибаешься. при наличии конкурирующих транзакций придётся каждый раз блокировать таблицу целиком. при весомых объёмах это очень чувствительно, даже на Оракле.
creator: МП
date: 2023-10-24T13:52:47-00:00

Re: Как построить FK на PK?
type: item
description: hvlad писал(а) Tue, 24 October 2023 16:40 МП писал(а) Tue, 24 October 2023 15:29 зы: у Оракела есть возможность "пристегнуть" существующий индекс к создаваемому CONSTRAINT при помощи предиката USING INDEX. у нас же, USING INDEX позволяет только задать имя создаваемого индекса для создаваемого CONSTRAINT. И это тоже мы хотим перенять. И может быть кое-что ещё. дык, где капу нажать? маякни, мы поддержим.
creator: МП
date: 2023-10-24T13:55:07-00:00

Re: Как построить FK на PK?
type: item
description: МП писал(а) Tue, 24 October 2023 16:52 при наличии конкурирующих транзакций придётся каждый раз блокировать таблицу целиком. Почему? Не понял. В какой момент происходит блокировка таблицы и почему? Там 2 операции - проверка (select) и вставка. Select вообще не может блокировать, а вставка... Вставка без первичного ключа блокирует всю таблицу, так, что ли? А при наличии первичного ключа не блокирует? Провел эксперимент. Таблица без PK. В одном коннекте добавил запись, транзакцию не закрыл, в другом коннекте добавил запись, подтвердил транзакцию по второму коннекту, подтвердил транзакцию по первому коннекту. Никаких конфликтов. Не видно, чтобы кто-то что-то блокировал. Или слово "придется" относится не к алгоритмам сервера, а к собственной "ручной" логике? Тогда не вижу необходимости блокировать, по-крайней мере, для своих задач.
creator: shalamyansky
date: 2023-10-24T14:31:44-00:00

Re: Как построить FK на PK?
type: item
description: shalamyansky Никаких конфликтов. Не видно, чтобы кто-то что-то блокировал. именно поэтому таким макаром обеспечить уникальность и не получится. блокировать надо принудительно на уровне транзакции. причём все транзакции которые "лезут" в эту таблицу со стороны приложения должны быть с одинаковым уровнем изоляции с указанием предиката RESERVING. по аналогии с семафорами - кто первый встал, того и тапочки. это всё нужно обеспечить на уровне приложений. но где гарантия, что никто не полезет в базу выставив себе другой уровень изоляции, и тем самым наплевав на стройную архитектуру выстроенного песочного замка.
creator: МП
date: 2023-10-24T14:55:50-00:00

Re: Как построить FK на PK?
type: item
description: shalamyansky Или слово "придется" относится не к алгоритмам сервера, а к собственной "ручной" логике? Тогда не вижу необходимости блокировать, по-крайней мере, для своих задач. "пронесёт" только если нет конкурирующих транзакций. да и то, не гарантировано.
creator: МП
date: 2023-10-24T15:00:34-00:00

Re: Как построить FK на PK?
type: item
description: Не понимаю. Я стараюсь, но я не понимаю. Вот такая процедура для вставки Показать скрытый текст create or alter procedure INSERT_INTO_SLAVE( ID bigint ) as begin if( not exists( select ID from SLAVE where ID = :ID ) )then begin insert into SLAVE ( ID ) values ( :ID ) ; end end Все клиенты вместо Показать скрытый текст insert into SLAVE ( ID ) values ( :ID ) ; вызывают Показать скрытый текст execute procedure INSERT_INTO_SLAVE( :ID ); Что тут может пойти не так? С любым уровнем изоляции и без всяких RESERVING? P.S. Все, понял. Виноват, протупил. Легко могут быть созданы 2 одинаковые записи. Даже проверил, могут. Вопрос снимаю.
creator: shalamyansky
date: 2023-10-24T15:30:22-00:00