Oracle所有外键引用

6 回答

• 18

  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'

  回复于 2024-04-29T09:09:54+08:00
• 0

  这里的 r_constraint_name 答案本身并不是Oracle的新手，但这有效：

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

  回复于 2024-04-29T09:09:54+08:00
• 0

  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列的额外谓词 .

  问候，Rob .

  回复于 2024-04-29T09:09:54+08:00
• 0

  好 . 这里有一个请求，为您提供引用的表和列：

  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（）函数 . 这不是我的情况 .

  回复于 2024-04-29T09:09:54+08:00
• 1

  我的问题略有不同 . 我有一个表，我想以编程方式知道它引用了哪些其他表/列 .

  我从上面的Stan的回答开始，但这并没有给我我需要的东西，所以我提出了这个，我在这里发布以防万一其他人有我的问题：

  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
  

  回复于 2024-04-29T09:09:54+08:00
• 22

  使用此查询 .

  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;
  

  回复于 2024-04-29T09:09:54+08:00