时间戳

首页 > 解决方案 > 查询查找主键和外键列以及代理键 oracle sql

oracle - 查询查找主键和外键列以及代理键 oracle sql

问题描述

我有两个问题

  1. 我需要找出整个数据库模式 oracle sql 中的所有代理键吗？
  2. 我需要找出整个数据库模式 oracle sql 中的所有主键和外键？

一些字段仅作为示例

COLUMNNAME              DATATYPE          NULLABLE COLUMN_ID COMMENTS
CCC_CUSTCAT_SID         NUMBER(38,0)        No      1        Unique SURROGATE Key
CCC_NUM_SUBCATEGORY_UID NUMBER(22,0)        No      2        NATURAL key column 1, subcategory number
CCC_CATEGORY_SID        NUMBER(38,0)        No      3        FK: Unique SURROGATE Key of Category
CCC_MOD_CATEGORY_UID    VARCHAR2(300 BYTE)  No      4        NATURAL key column 2, Category UID
KCD_SUBSCR_TYPE_SID     NUMBER(38,0)        No      5        FK: Unique SURROGATE Key of SUBSCR type
KCD_MOD_SUBSCR_TYPE_UID VARCHAR2(300 BYTE)  No      6        NATURAL KEY column 3, SUBSCR type UID

所以我有些困惑代理键与自然键与主键与外键之间的真正区别是什么？当试图通过各种帖子来理解搜索时，它有点令人困惑？

基本了解

PK - 不是唯一标识的空字段 FK - 用于引用

自然键 - ? 代理键 - ? UID - ? 西德 - ?

我找到了一些资料

有人可以解释并提供查询问题 - 1 和问题 - 2

我尝试了一些东西，但出现错误

SELECT ac.table_name,
         column_name,
         position,
         ac.constraint_name,
         DECODE (constraint_type, 'P', 'Primary Key', 'Foreign Key') key_type,
         (SELECT ac2.table_name
            FROM all_constraints ac2
           WHERE AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME)
            fK_to_table
    FROM all_cons_columns acc, all_constraints ac
   WHERE     acc.constraint_name = ac.constraint_name
         AND acc.table_name = ac.table_name
         AND CONSTRAINT_TYPE IN ('P', 'R')
         --AND ac.table_name = 'ACCOUNT' (your table here)
ORDER BY table_name, constraint_type, position;

ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

标签： oracleoracle-sql-data-modeler

解决方案

您的标量查询

(SELECT ac2.table_name
            FROM all_constraints ac2
           WHERE AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME)

可以返回多于一行，因为多于 1 个表可以引用父表上的相同主键约束。

一个快速的解决方法是将它们全部收集起来，即

SELECT ac.table_name,
         column_name,
         position,
         ac.constraint_name,
         DECODE (constraint_type, 'P', 'Primary Key', 'Foreign Key') key_type,
         (SELECT listagg(ac2.table_name) 
            FROM all_constraints ac2
           WHERE AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME)
            fK_to_table
    FROM all_cons_columns acc, all_constraints ac
   WHERE     acc.constraint_name = ac.constraint_name
         AND acc.table_name = ac.table_name
         AND CONSTRAINT_TYPE IN ('P', 'R')
         --AND ac.table_name = 'ACCOUNT' (your table here)
ORDER BY table_name, constraint_type, position;

推荐阅读