Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 FOREIGN KEY на ту же таблицу??  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
Сегодня обнаружил в базе очень интересную вещь :)
База чужая, купленная, но полностью открытая.

Есть таблица TABLE1. В ней три колонки (например): A, B, COMMENTS. Судя по их логике, колонка A - мастеровое значение, B - детальное. Объявлен FOREIGN CONSTRAINT от колонки B на колонку A в той же самой таблице! Блин, у меня бы никогда мысли в такую сторону не повернулись. Кто-нибудь так делал? Как впечатления? По-моему, это ненормально с точки зрения эффективной разработки базы (нагло позаимствовал термин у Кайта, может быть, не очень точно). Разве реляционная модель рассматривает такие отношения как цикличные связи сущности самой с собой?

На таблице еще периодически дэдлоки возникают. Думали, надо индекс создать на внешнем ключе (колонка B; A - и так первичный ключ). Создали... потом обнаружили, что все значения в колонке B - NULL! То бишь, индекс пустой. Собссно, что полагается блокировать ораклу, если индекс пустой? :)

И еще... если foreign constraint создан с ON DELETE CASCADE... мозги закручиваются...
29 ноя 06, 03:59    [3464618]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Вадиман
Есть таблица TABLE1. В ней три колонки (например): A, B, COMMENTS. Судя по их логике, колонка A - мастеровое значение, B - детальное. Объявлен FOREIGN CONSTRAINT от колонки B на колонку A в той же самой таблице! Блин, у меня бы никогда мысли в такую сторону не повернулись. Кто-нибудь так делал? Как впечатления? По-моему, это ненормально с точки зрения эффективной разработки базы (нагло позаимствовал термин у Кайта, может быть, не очень точно). Разве реляционная модель рассматривает такие отношения как цикличные связи сущности самой с собой?
Почему нет?
Стандартная оракловая демка (SCOTT/TIGER) содержит табличку EMP
Там присутствует как код работника (EMPNO), так и его начальника (MGR)
Вполне логично повесить внешний ключ на предмет того, что манагера или нет (круче всех), либо только из тех, сотрудников, которые есть в этой табличке (считай, из числа сотрудников предприятия)

Вадиман
На таблице еще периодически дэдлоки возникают. Думали, надо индекс создать на внешнем ключе (колонка B; A - и так первичный ключ). Создали... потом обнаружили, что все значения в колонке B - NULL! То бишь, индекс пустой. Собссно, что полагается блокировать ораклу, если индекс пустой? :)
Как раз он и будет знать, что не надо ничего блокировать (кстати, с 9.2.0.4(?) индекс по внешнему ключу уже не критичен, а иногда и вреден)

Вот только к deadlock непонятно какое это отношение имеет -- там скорее всего неправильная последовательность блокирования строк в приложении
Вадиман
И еще... если foreign constraint создан с ON DELETE CASCADE... мозги закручиваются...
Какие сложности ? Каскад так каскад
29 ноя 06, 04:14    [3464620]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
4uko
Member

Откуда:
Сообщений: 276
Вячеслав Любомудров
(кстати, с 9.2.0.4(?) индекс по внешнему ключу уже не критичен, а иногда и вреден)

Вячеслав об этом можно подробней ? Где почитать ? Я то по старинке как дядюшка Кайт советовал - ставлю индекс
29 ноя 06, 13:16    [3466592]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
Вадиман
Есть таблица TABLE1. В ней три колонки (например): A, B, COMMENTS. Судя по их логике, колонка A - мастеровое значение, B - детальное. Объявлен FOREIGN CONSTRAINT от колонки B на колонку A в той же самой таблице! Блин, у меня бы никогда мысли в такую сторону не повернулись. Кто-нибудь так делал? Как впечатления? По-моему, это ненормально с точки зрения эффективной разработки базы (нагло позаимствовал термин у Кайта, может быть, не очень точно). Разве реляционная модель рассматривает такие отношения как цикличные связи сущности самой с собой?
Стандартная модель. Описывает иерархические связи.
Такая связь имеет жаргонное название - "свиное ухо". :-)
29 ноя 06, 13:20    [3466624]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
Stax.
Guest
Вячеслав Любомудров

Как раз он и будет знать, что не надо ничего блокировать (кстати, с 9.2.0.4(?) индекс по внешнему ключу уже не критичен, а иногда и вреден)

я б сказал не так критичен
Всетаки без индекса иногода "блокирует"
......
stax
29 ноя 06, 14:54    [3467325]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
4uko
Вячеслав Любомудров
(кстати, с 9.2.0.4(?) индекс по внешнему ключу уже не критичен, а иногда и вреден)

Вячеслав об этом можно подробней ? Где почитать ? Я то по старинке как дядюшка Кайт советовал - ставлю индекс
Заметь, я не сказал не нужен, я сказал не критичен
Нужен ли для внешних ключей индекс
Deadlock при наличии индекса на внешний ключ

Stax.
Вячеслав Любомудров

Как раз он и будет знать, что не надо ничего блокировать (кстати, с 9.2.0.4(?) индекс по внешнему ключу уже не критичен, а иногда и вреден)

я б сказал не так критичен
Всетаки без индекса иногода "блокирует"
......
stax
Я бы по другому процитировал
Вячеслав Любомудров
Вадиман
...
Создали... потом обнаружили, что все значения в колонке B - NULL! То бишь, индекс пустой. Собссно, что полагается блокировать ораклу, если индекс пустой? :)
Как раз он и будет знать, что не надо ничего блокировать (кстати, с 9.2.0.4(?) индекс по внешнему ключу уже не критичен, а иногда и вреден)
30 ноя 06, 02:06    [3470118]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
Цитата из доки по Oracle 10g (Application Development Guide):

<
The following restrictions apply to bulk loading with version-enabled tables in the current release:

* Bulk loading into a table with a self-referential integrity constraint is not allowed.
...
>

Речь идет про Workspace Manager.
Вот и тараканы полезли. Может, в обычных операциях oracle и выдерживает такой изврат как self-reference foreign constraint, но если что-то противоречит теории реляционных баз данных - где-нибудь вылезет. Может, я неправ, поправьте тогда, пожалуйста... отношения между сущностями не могут быть "цикличными". Вышеприведенный пример (таблица-дерево из трех колонок ID, PARENT_ID, NAME) по теории надо делать так:

TABLE1 (id, name)
TABLE2 (id, parent_id)

Вторую таблицу связей TABLE2 людям лень делать, отсюда и проблемы.
4 дек 06, 11:08    [3485891]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Вадиман
Сегодня обнаружил в базе очень интересную вещь :)
База чужая, купленная, но полностью открытая.

Есть таблица TABLE1. В ней три колонки (например): A, B, COMMENTS. Судя по их логике, колонка A - мастеровое значение, B - детальное. Объявлен FOREIGN CONSTRAINT от колонки B на колонку A в той же самой таблице! Блин, у меня бы никогда мысли в такую сторону не повернулись. Кто-нибудь так делал? Как впечатления? По-моему, это ненормально с точки зрения эффективной разработки базы (нагло позаимствовал термин у Кайта, может быть, не очень точно). Разве реляционная модель рассматривает такие отношения как цикличные связи сущности самой с собой?


А дерево, которое обрабатывают с помощью CONNECT BY - это разве не типичаня ситуация где это может быть реализовано?
4 дек 06, 12:18    [3486552]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
Вадиман
Цитата из доки по Oracle 10g (Application Development Guide):

<The following restrictions apply to bulk loading with version-enabled tables in the current release:
* Bulk loading into a table with a self-referential integrity constraint is not allowed....>

Это говорит всего лишь об ограничениях текущей версии при массовой загрузке для версионных таблиц. Не более того.
Вадиман
Вот и тараканы полезли.
Чьи и куда? :-)))
Вадиман
Может, в обычных операциях oracle и выдерживает такой изврат как self-reference foreign constraint, но если что-то противоречит теории реляционных баз данных - где-нибудь вылезет. Может, я неправ, поправьте тогда, пожалуйста... отношения между сущностями не могут быть "цикличными". Вышеприведенный пример (таблица-дерево из трех колонок ID, PARENT_ID, NAME) по теории надо делать так:

TABLE1 (id, name)
TABLE2 (id, parent_id)

Вторую таблицу связей TABLE2 людям лень делать, отсюда и проблемы.
Еще раз повторю. Никакого противоречия с теорией реляционных БД нет.
Это классическая связь сущности самой с собой, жаргонно называемая "свиным ухом".
Не фантазируйте! Изучите эту самую теорию реляционных баз данных.
Изгоните извращенных тараканов из своей головы! :-)

Например можно почитать С.Кузнецов. Основы современных баз данных
4 дек 06, 12:46    [3486818]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
Но лучше почитать один из первоисточников:

К. Дж. Дейт: Введение в системы баз данных
4 дек 06, 13:49    [3487356]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
softy
Вадиман
Сегодня обнаружил в базе очень интересную вещь :)
База чужая, купленная, но полностью открытая.

Есть таблица TABLE1. В ней три колонки (например): A, B, COMMENTS. Судя по их логике, колонка A - мастеровое значение, B - детальное. Объявлен FOREIGN CONSTRAINT от колонки B на колонку A в той же самой таблице! Блин, у меня бы никогда мысли в такую сторону не повернулись. Кто-нибудь так делал? Как впечатления? По-моему, это ненормально с точки зрения эффективной разработки базы (нагло позаимствовал термин у Кайта, может быть, не очень точно). Разве реляционная модель рассматривает такие отношения как цикличные связи сущности самой с собой?


А дерево, которое обрабатывают с помощью CONNECT BY - это разве не типичаня ситуация где это может быть реализовано?


да дерево-то нормально :). Речь идет об ограничениях целостности

Читал я теорию, и честное слово, ну не помню я упоминаний про "свиные части тела" :) Может, конечно, леший попутал, забыл... сейчас полезу вновь...
5 дек 06, 12:13    [3491918]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
К.Дейт:
"Атрибут отношения R1 является внешним ключом,
если этот ключ не первичный ключ отношения R1.
но его значения являются значениями первичного ключа
некоторого отношения R2.
R1 и R2 не обязательно различны"

В случае, если R1 и R2 различны, определение можно смягчить,
убрав слова "если этот ключ не первичный ключ отношения R1"
Для данного случая (если R1 и R2 различны) получим:
"Атрибут отношения R1 является внешним ключом,
если его значения являются значениями первичного ключа
некоторого отношения R2."
5 дек 06, 12:33    [3492074]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Вадиман
softy
А дерево, которое обрабатывают с помощью CONNECT BY - это разве не типичаня ситуация где это может быть реализовано?


да дерево-то нормально :). Речь идет об ограничениях целостности


Для чего необходим FK? Что-бы нельзя было добавить потомка, ссылающегося на несуществующего предка, нельзя было удалить предка, если есть потомки. Дерево - пример подобной ситуации, часто используемый в Internet-магазинах для реализации каталога. Таким образом нельзя будет добавить товар или раздел без указания раздела верхнего уровня, т.е родителя.

Если, скажем ты хочешь добавить товар "Антоновка", но раздела "Яблоки"(или вообще всей ветки Продукты->Фрукты->Яблоки) у тебя ещё нет, то ты не сможешь добавить товар(а если добавишь, то только по-глупости в другой, неподходящий раздел).
Или захочешь удалить раздел "Яблоки", то при наличии товаров(потомков) - тебе это не удастся - иди удаляй по каждому виду яблок.

Это и есть ограничение целостности.

Причём наличие индекса по parent_id - только увеличить производительность при создании дерева для отображения, так как в запросе нужно будет соединять
parent_id = id

Пример с реальными таблицами и данными я уж не буду создавать. Вроде и так всё понятно должно быть.
5 дек 06, 16:23    [3494165]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
SQL*Plus
К.Дейт:
"Атрибут отношения R1 является внешним ключом,
если этот ключ не первичный ключ отношения R1.
но его значения являются значениями первичного ключа
некоторого отношения R2.
R1 и R2 не обязательно различны"

В случае, если R1 и R2 различны, определение можно смягчить,
убрав слова "если этот ключ не первичный ключ отношения R1"
Для данного случая (если R1 и R2 различны) получим:
"Атрибут отношения R1 является внешним ключом,
если его значения являются значениями первичного ключа
некоторого отношения R2."


Пытаюсь переварить... спасибо!
Мм... тут идет речь про отношения между сущностями, не так ли? Но не про саму сущность... сейчас сяду с листком порисовать :)
6 дек 06, 03:21    [3496189]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
softy
Вадиман
softy
А дерево, которое обрабатывают с помощью CONNECT BY - это разве не типичаня ситуация где это может быть реализовано?


да дерево-то нормально :). Речь идет об ограничениях целостности


Для чего необходим FK? Что-бы нельзя было добавить потомка, ссылающегося на несуществующего предка, нельзя было удалить предка, если есть потомки. Дерево - пример подобной ситуации, часто используемый в Internet-магазинах для реализации каталога. Таким образом нельзя будет добавить товар или раздел без указания раздела верхнего уровня, т.е родителя.

Если, скажем ты хочешь добавить товар "Антоновка", но раздела "Яблоки"(или вообще всей ветки Продукты->Фрукты->Яблоки) у тебя ещё нет, то ты не сможешь добавить товар(а если добавишь, то только по-глупости в другой, неподходящий раздел).
Или захочешь удалить раздел "Яблоки", то при наличии товаров(потомков) - тебе это не удастся - иди удаляй по каждому виду яблок.

Это и есть ограничение целостности.

Причём наличие индекса по parent_id - только увеличить производительность при создании дерева для отображения, так как в запросе нужно будет соединять
parent_id = id

Пример с реальными таблицами и данными я уж не буду создавать. Вроде и так всё понятно должно быть.


Сорри, да это понятно :)
Меня просто смущало, что в моем примере мастер и деталь - абсолютно разные в общем случае сущности - находились в одной таблице. Всегда считал так: одна сущность - одна таблица. Ну, плюс возможные таблицы на ее беспорядочные связи.
6 дек 06, 03:24    [3496190]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
ModelR
Member

Откуда: Нижний Новгород
Сообщений: 1798
Вадиман
Вышеприведенный пример (таблица-дерево из трех колонок ID, PARENT_ID, NAME) по теории надо делать так:

TABLE1 (id, name)
TABLE2 (id, parent_id)

Вторую таблицу связей TABLE2 людям лень делать, отсюда и проблемы.
Вторую таблицу нельзя не делать только если отношение многие-ко многим (типа спецификация, aka BOM). По теории она необходима и при чисто деревянной структуре - но если придерживатся строгой версии, запрещающей NULL в первичных таблицах.
Связи же сущности с собой нигде не запрещены.
6 дек 06, 09:37    [3496646]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
softy
Member

Откуда: from Russia
Сообщений: 5911
Вадиман

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


Неправильное понимание. Есть логическая структура, есть физическая. Физическая реализация не всегда будет точно соответствовать логической. Причём в разных СУБД физическая реализация может быть разной.
6 дек 06, 11:10    [3497317]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
Вадиман
SQL*Plus
К.Дейт:
"Атрибут отношения R1 является внешним ключом,
если этот ключ не первичный ключ отношения R1.
но его значения являются значениями первичного ключа
некоторого отношения R2.
R1 и R2 не обязательно различны"

В случае, если R1 и R2 различны, определение можно смягчить,
убрав слова "если этот ключ не первичный ключ отношения R1"
Для данного случая (если R1 и R2 различны) получим:
"Атрибут отношения R1 является внешним ключом,
если его значения являются значениями первичного ключа
некоторого отношения R2."
Пытаюсь переварить... спасибо!
Мм... тут идет речь про отношения между сущностями, не так ли? Но не про саму сущность... сейчас сяду с листком порисовать :)
Нет, не так!!!

Речь идет об отношениях (relations), которые моделируют (отображают) как сущности (entities), так и связи (relationships) между сущностями.

Реляционная модель данных названа таковой, так как базируется на теории отношений.

К.Дейт:

"Дана совокупность множеств D1, D2, ...Dn (не обязательно различных).

Отношение R, определенное на этих множествах,
есть множество упорядоченных кортежей (d1, d2, ... dn) таких,
что d1 принадлежит D1, d2 принадлежит D2, ... dn принадлежит Dn.

Множества D1, D2, ...Dn называются доменами отношения.

Величина n называется степенью отношения.

Количество кортежей в отношении - кардинальное число отношения."

Атрибут представляет использование домена внутри отношения".

Отношения удобно изображать в виде таблиц,
где атрибуты - это столбцы,
а кортежи - это строки таблицы.

Пример 1. Отношение PART (реляционная схема).
DOMAIN P# CHARACTER(6)... /* Множество номеров деталей */
DOMAIN PNAME CHARACTER(20)... /* Множество названий деталей */
DOMAIN COLOR CHARACTER(16)... /* Множество названий цветов */
DOMAIN WEIGHT NUMERIC(10)... /* Множество весов деталей */
DOMAIN CITY CHARACTER(24)... /* Множество начеленных пунктов */

RELATION PART(PARTNO DOMAIN P#,
              PRTNAM DOMAIN PNAME,
              COLOR DOMAIN COLOR,
              WT DOMAIN WEIGHT,
              LOC DOMAIN CITY)
Пример 2. Отношение COMPONENT (реляционная схема).
DOMAIN P# CHARACTER(6)... /* Множество номеров деталей */
DOMAIN QUANTITY NUMERIC(10)... /* Множество значений количества деталей */

RELATION COMPONENT(MAJOR DOMAIN P#, /* Главная деталь, содержащая детали-составляющие  */
                   MINOR DOMAIN P#,      /* Деталь-составляющая, входящая в главную деталь */
                   QUANTITY DOMAIN QUANTITY /* Количество деталей */
                 )
6 дек 06, 11:28    [3497503]     Ответить | Цитировать Сообщить модератору
 Re: FOREIGN KEY на ту же таблицу??  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
Разобрались таки с теорией? :-)
7 дек 06, 10:02    [3502580]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить