OceanBase 数据库 Oracle 模式查看外键约束关系

本文讲述如何查看 OceanBase 数据库 Oracle 模式的外键约束关系。

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

操作步骤

登录 sys 用户使用如下 SQL 可分别查看有外键约束的表以及外键约束的父子关系。

1. 先创建两张测试表。

   obclient [SYS]> create table t1(id int primary key);
   Query OK, 0 rows affected (0.237 sec)
   

   obclient [SYS]> create table t2(id int,id_t1 int,foreign key(id_t1) references t1(id));
   Query OK, 0 rows affected (0.365 sec) 
   

2. 查看外键约束的表。

   obclient [SYS]> SELECT * FROM DBA_CONSTRAINTS WHERE OWNER IN ( SELECT USER_NAME FROM ALL_VIRTUAL_USER_REAL_AGENT WHERE IS_LOCKED=0 AND USER_NAME NOT IN ( 'LBACSYS',  'ORAAUDITOR', 'PUBLIC','DBA','CONNECT','RESOURCE' ) )      AND CONSTRAINT_TYPE = 'R'  AND STATUS = 'ENABLED'\G
   

   输出结果如下。

        *************************** 1. row ***************************
                  OWNER: SYS
        CONSTRAINT_NAME: T2_OBFK_1689908449892346
        CONSTRAINT_TYPE: R
             TABLE_NAME: T2
        SEARCH_CONDITION: NULL
                  R_OWNER: SYS
        R_CONSTRAINT_NAME: T1_OBPK_1689908447400409
             DELETE_RULE: NO ACTION
                  STATUS: ENABLED
             DEFERRABLE: NOT DEFERRABLE
             DEFERRED: IMMEDIATE
             VALIDATED: VALIDATED
             GENERATED: NULL
                  BAD: NULL
                  RELY: NULL
             LAST_CHANGE: NULL
             INDEX_OWNER: NULL
             INDEX_NAME: NULL
                  INVALID: NULL
             VIEW_RELATED: NULL
        1 row in set (0.003 sec)
   

3. 查看外键约束父子关系。

     obclient [SYS]> SELECT A.OWNER       外键拥有者,
            A.TABLE_NAME  外键表,
             C.COLUMN_NAME 外键列,
             B.OWNER       主键拥有者,
             B.TABLE_NAME  主键表,
             D.COLUMN_NAME 主键列
        FROM DBA_CONSTRAINTS  A,
             DBA_CONSTRAINTS  B,
             DBA_CONS_COLUMNS C,
             DBA_CONS_COLUMNS D
       WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
         AND A.CONSTRAINT_TYPE = 'R'
         AND A.R_OWNER = B.OWNER
         AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
         AND B.CONSTRAINT_NAME = D.CONSTRAINT_NAME
         AND A.OWNER = C.OWNER
         AND A.TABLE_NAME = C.TABLE_NAME
         AND B.OWNER = D.OWNER
         AND B.TABLE_NAME = D.TABLE_NAME
         AND A.OWNER = 'SYS';
   

   输出结果如下。

    +-----------------+-----------+-----------+-----------------+-----------+-----------+
    | 外键拥有者      | 外键表    | 外键列    | 主键拥有者      | 主键表    | 主键列    |
    +-----------------+-----------+-----------+-----------------+-----------+-----------+
    | SYS             | T2        | ID_T1     | SYS             | T1        | ID        |
    +-----------------+-----------+-----------+-----------------+-----------+-----------+
    1 row in set (0.074 sec)