Oracle все ссылки на внешние ключи

Я унаследовал схему, поэтому не имею полного знания/уверенности в том, что там есть.

У меня есть проектная таблица с projectId. Существует целая куча других таблиц, которые ссылаются на эту таблицу по id проекта. Я хочу выполнить запрос, чтобы установить:

  • Какие таблицы содержат ссылки на внешние ключи в таблице проектов на id проекта
  • В каких таблицах есть столбец с именем project id (в случайные внешние ключи не настроены).

Если это был SQL Server, я знаю, как запрашивать метаданные, но как это сделать в Oracle?

Ответы

Ответ 1

1)

select table_name
  from all_constraints
 where r_constraint_name = [your pk/uk constraint on PROJECTS(id)]

2)

select table_name
  from all_tab_columns
 where column_name = 'PROJECT_ID'

Вы можете добавить дополнительный предикат, содержащий столбец OWNER.

С уважением, Роб.

Ответ 2

Ok. Вот запрос, который даст вам таблицу и столбец, на которые ссылается:

SELECT
 c_list.CONSTRAINT_NAME as NAME,
 substr(c_src.COLUMN_NAME, 1, 20) as SRC_COLUMN,
 c_dest.TABLE_NAME as DEST_TABLE,
 substr(c_dest.COLUMN_NAME, 1, 20) as DEST_COLUMN
FROM ALL_CONSTRAINTS c_list, ALL_CONS_COLUMNS c_src, ALL_CONS_COLUMNS c_dest
WHERE c_list.CONSTRAINT_NAME   = c_src.CONSTRAINT_NAME
 AND  c_list.OWNER             = c_src.OWNER
 AND  c_list.R_CONSTRAINT_NAME = c_dest.CONSTRAINT_NAME
 AND  c_list.OWNER             = c_dest.OWNER
 AND  c_list.CONSTRAINT_TYPE = 'R'
 AND  c_src.OWNER      = '<your-schema-here>'
 AND  c_src.TABLE_NAME = '<your-table-here>'
GROUP BY c_list.CONSTRAINT_NAME, c_src.TABLE_NAME,
    c_src.COLUMN_NAME, c_dest.TABLE_NAME, c_dest.COLUMN_NAME;

Что даст вам что-то вроде этого:

NAME                  |SRC_COLUMN      |DEST_TABLE            | DEST_COLUMN
----------------------|----------------|----------------------|-----------
CFK_RUB_FOR           |FOR_URN         |T03_FORMAT            |FOR_URN
CFK_RUB_RUB           |RUB_RUB_URN     |T01_RUBRIQUE          |RUB_URN
CFK_RUB_SUP           |SUP_URN         |T01_SUPPORT           |SUP_URN
CFK_RUB_PRD           |PRD_URN         |T05_PRODUIT           |PRD_URN

Вы можете забыть функцию substr(), если результат без использования. Это не мое дело.

Ответ 3

1): SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME='R' and R_CONSTRAINT_NAME='xxx'

где xxx - это имя ограничения первичного ключа в таблице проекта

2): SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='PROJECT_ID'

Ответ 4

Ответы r_constraint_name здесь, похоже, не работают для меня, не знаю, почему, поскольку я новичок в Oracle самостоятельно, но это сработало:

SELECT * FROM ALL_CONSTRAINTS WHERE CONSTRAINT_NAME = '<constraint>';

Ответ 5

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

Я начал с ответа Стэна выше, но это не дало мне точно, что мне было нужно, поэтому я придумал это, и я отправляю его здесь, если у кого-то еще есть моя проблема:

WITH src as
(SELECT ac.table_name, ac.constraint_name, accs.column_name, accs.position, ac.r_constraint_name
  FROM ALL_CONSTRAINTS ac, all_cons_columns accs
 WHERE ac.owner = '<owner>'
   AND ac.constraint_type = 'R'
   AND ac.table_name = '<src_table>'
   AND accs.owner = ac.owner
   AND accs.table_name = ac.table_name
   AND accs.constraint_name = ac.constraint_name
ORDER BY ac.table_name, ac.constraint_name, accs.position),
dst as
(SELECT ac.table_name, ac.constraint_name, accs.column_name, accs.position
  FROM ALL_CONSTRAINTS ac, all_cons_columns accs
 WHERE ac.owner = '<owner>'
   AND accs.owner = ac.owner
   AND accs.table_name = ac.table_name
   AND accs.constraint_name = ac.constraint_name
ORDER BY ac.table_name, ac.constraint_name, accs.position)
SELECT src.table_name as src_table,
       dst.table_name as dst_table,
       src.constraint_name as src_constraint,
       src.column_name as src_column,
       dst.column_name as dst_column,
       src.position as position
  FROM src,dst
 WHERE src.r_constraint_name = dst.constraint_name
   AND src.position = dst.position

Ответ 6

Используйте этот запрос.

   select b.TABLE_NAME,b.CONSTRAINT_NAME ,a.COLUMN_NAME
   from all_constraints b, all_cons_columns a
   where r_constraint_name = 'Constraint_Name' and  a.CONSTRAINT_NAME=b.CONSTRAINT_NAME;