Выпуск 27

Поиск связей главная-подчиненная между таблицами

Уважаемые подписчики! Большое спасибо всем, кто откликнулся на мою просьбу как-то прокомментировать работу рассылки. Тем из вас, кто писал в гостевую, я постарался ответить. Остальным, возможно, еще отвечу подробнее. Вероятно, в одном из следующих выпусков подведу и формальные итоги. Пока же прокомментирую полученные отзывы так.

Большинство ответивших считает, что выпуски, посвященные формальному синтаксису, не нужны. Тем не менее, они изредка будут выходить, хотя бы потому, что представленные в документации синтаксические диаграммы во многих случаях неудобно использовать.

Рассылка не предназначалась для начинающих. Но, я собираюсь опубликовать серию выпусков об основных концепциях Oracle, основных задачах администрирования и способах их решения.

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

Этот выпуск посвящен поиску всех отношений главная-подчиненная между таблицами базы данных. Последний раз Том Кайт вернулся к этому вопросу 10 января 2003 года. Выпуск выходит намного позже обещанного, но тому были свои причины, о которых можно почитать здесь.

Получение списка всех отношений главная-подчиненная между таблицами.

Том!

Не мог бы ты объяснить, как получить список всех отношений главная-подчиненная между таблицами базы данных.

В списке сначала должны идти таблицы, не являющиеся подчиненными, а в конце - не имеющие подчиненных.

Например,

Главная                         Подчиненная
----------------------------------------------------------------
 BDP_INFO                       BAA
 CONTR                          ABP
 CONTR                          B_INCE
 CONTR                          BDP_INFO

Таблица BDP_INFO является главной по отношению к BAA, и одновременно подчиненной для CONTR. Поэтому список должен иметь следующий вид:

Contr ---------> BDP_info
BDP_info ------> BAA
...

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

Ответ Тома Кайта

Если НЕТ требований целостности, ссылающихся на ту же таблицу:

create table emp (empno int primary key, mgr int references emp(empno));

подойдет следующее решение:

Для упрощения решения необходимо использовать временную таблицу -- нельзя выполнять запрос connect by по соединению, поэтому необходимо скопировать user_constraints:

tkyte@TKYTE816> create table p (x int primary key);

Table created.

tkyte@TKYTE816> create table c1 (x primary key references p);

Table created.

tkyte@TKYTE816> create table c2 (x primary key references c1);

Table created.

tkyte@TKYTE816> create table c3 (x primary key references c2);

Table created.

tkyte@TKYTE816> create table c4 (x primary key references c2);

Table created.

tkyte@TKYTE816> create global temporary table temp_constraints
  2  as
  3  select a.table_name,
  4         a.constraint_name pkey_constraint,
  5             b.constraint_name fkey_constraint,
  6             b.r_constraint_name
  7    from user_constraints a, user_constraints b
  8   where 1=0
  9  /

Table created.

tkyte@TKYTE816> alter table temp_constraints modify fkey_constraint NULL;

Table altered.

tkyte@TKYTE816> delete from temp_constraints;

0 rows deleted.

tkyte@TKYTE816> insert into temp_constraints
  2  select table_name, constraint_name, null, null from user_constraints where constraint_type
  3  = 'P';

5 rows created.

tkyte@TKYTE816> insert into temp_constraints
  2  select a.table_name,
  3         a.constraint_name pkey_constraint,
  4             b.constraint_name fkey_constraint,
  5             b.r_constraint_name
  6    from user_constraints a, user_constraints b
  7   where a.table_name = b.table_name
  8     and a.constraint_type = 'P'
  9     and b.constraint_type = 'R'
 10  /

4 rows created.

tkyte@TKYTE816> column table_name format a2
tkyte@TKYTE816> select * from temp_constraints;

TA PKEY_CONSTRAINT                FKEY_CONSTRAINT                
R_CONSTRAINT_NAME
-- ------------------------------ ------------------------------ 
------------------------------
C1 SYS_C004927
C2 SYS_C004929
C3 SYS_C004931
C4 SYS_C004933
P  SYS_C004926
C1 SYS_C004927                    SYS_C004928                    SYS_C004926
C2 SYS_C004929                    SYS_C004930                    SYS_C004927
C3 SYS_C004931                    SYS_C004932                    SYS_C004929
C4 SYS_C004933                    SYS_C004934                    SYS_C004929

9 rows selected.

tkyte@TKYTE816> column table_name format a10
tkyte@TKYTE816> select rpad('*', (level-1)*2, '*') || table_name table_name
  2    from temp_constraints
  3    start with fkey_constraint is null
  4   connect by prior pkey_constraint = r_constraint_name
  5  /

TABLE_NAME
----------
C1
**C2
****C3
****C4
C2
**C3
**C4
C3
C4
P
**C1
****C2
******C3
******C4

14 rows selected.

Как получить операторы ЯОД по словарю данных?

Том, я был бы очень благодарен, если бы ты подсказал, как написать сценарий, выбирающий операторы ЯОД, CREATE TABLE и т.д. для всех доступных объектов из словаря данных.

Я знаю, что могу получить эту информацию с помощью утилиты EXP, но я хотел бы по-своему сформатировать результат.

Если я не ошибаюсь, где-то на сайте Oracle есть похожий сценарий. Не пришлете ссылку?

Ответ Тома Кайта

Лично я о таком сценарии ничего не знаю -- я использую утилиту EXP (на самом деле, я использую утилиту exp ТОЛЬКО для получения информации о чужих схемах, поскольку я всегда поддерживаю свои сценарии в актуальном состоянии и выбирать операторы ЯОД из базы мне не нужно)...

В любом случае, в версии 9i есть соответствующая возможность:

scott@ORA9I.WORLD> select dbms_metadata.get_ddl('TABLE', 'EMP') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
  REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE NOVALIDATE,
         CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO")
  REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

Ошибка ORA-01436

Я попытался использовать ваше решение и получил следующее сообщение об ошибке.

ERROR at line 2:
ORA-01436: CONNECT BY loop in user data

И еще, нельзя ли указать условие в иерархическом запросе, например, чтобы выбирать информацию только о таблицах, имена которых начинаются с 'HEDGE'

Ответ Тома Кайта

Нет ли в вашей схеме внешних ключей, ссылающихся на ту же таблицу? Если да, представленное решение не сработает (как я и упоминал в ответе)...

Да, а условия в запрос с конструкцией connect by добавить можно:

where tname like 'HEDGE%'
...
start with tname like 'HEDGE%'
...
connect by prior ..... and tname like 'HEDGE%'
...

Но может оказаться проще сначала скопировать информацию по таблицам с именами типа HEDGE%. Но напомню, что если таблица ссылается сама на себя, получится цикл connect by, и представлденное решение НЕ сработает.

А зачем вставлять в таблицу дважды?

Сначала ты вставляешь в таблицу имена таблиц и ограничений типа 'P', а что ты вставляешь второй раз?

Ответ Тома Кайта

Сначала я вставляю первичные ключи (constraint_type = 'P')

Затем я вставляю информацию о первичных ключах и ссылающихся на них внешних ключах (constraint_type = 'R')

Как исключить ссылки на самих себя

Том, мне кажется, что исключить внешние ключи, ссылающиеся на первичный ключ той же таблицы, несложно:

    select a.table_name,
           a.constraint_name pkey_constraint,
           b.constraint_name fkey_constraint,
           b.r_constraint_name
      from user_constraints a, user_constraints b
     where a.table_name = b.table_name
       and a.constraint_type = 'P'
       and b.constraint_type = 'R'
       and a.constraint_name != b.r_constraint_name
/

... если я ничего не упустил, конечно.

Твой запрос не выбирает внешние ключи, ссылающиеся на другие уникальные ключи таблицы, а не на первичный. Ты считаешь, что так задавать внешние ключи неправильно?

Ответ Тома Кайта

Ну, почему же "неправильно" - просто такое нечасто встречается. Можно легко учесть эту ситуацию, добавив функцию decode по полю a.constraint_type, которая будет делать значения P и U "одинаковыми".

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2002 Oracle Corporation

В следующем выпуске

Результаты опроса подписчиков. Возможно, первый из серии авторских выпусков, посвященных индексам. Или перевод очередного блестящего ответа Тома Кайта... Следите за новостями на сайте проекта Open Oracle.

С наилучшими пожеланиями,

  В.К.

OpenXS Rambler's Top100 Rambler's Top100