达梦数据库获取主外键约束信息

达梦数据库获取约束信息

在达梦数据库中，我们有时候需要查询主外键信息，通过如下的SQL语句可以直接获取

--DM7获取主键信息
select
        t1.table_name     ,
        t1.constraint_name,
        t2.column_name
from
        all_constraints t1,
        dba_cons_columns t2
where
        t1.constraint_name=t2.constraint_name
    and t1.constraint_type='P'
order by
        t2.position asc
--主键语句
select 'alter table '||owner||'.'||table_name||' add constraint '||constraint_name||' primary key('||wm_concat(column_name)||');'
from 
(
select
        t1.owner,
        t1.table_name     ,
        t1.constraint_name,
         t2.column_name 
from
        all_constraints t1,
        dba_cons_columns t2
where
        t1.constraint_name=t2.constraint_name
    and t1.constraint_type='P'
order by
        t2.position asc
)
group by 
         owner,
         table_name     ,
         constraint_name
order by 
      owner,
      table_name     ,
      constraint_name
--DM7获取外键信息
select
        FK_OWNER,
        FK_TABLE,
        FK      ,
        FK_COL  ,
        PK_OWNER,
        PK_TABLE,
        B.PK_COL
FROM
        (
                SELECT
                        t1.owner             AS FK_OWNER,
                        t1.table_name        AS FK_TABLE,
                        t1.constraint_name   AS FK      ,
                        t3.column_name       AS FK_COL  ,
                        T2.OWNER             AS PK_OWNER,
                        t2.table_name        AS PK_TABLE,
                        t1.r_constraint_name AS PK      ,
                        T1.DELETE_RULE
                from
                        dba_constraints t1,
                        dba_constraints t2,
                        DBA_CONS_COLUMNS t3
                where
                        t1.constraint_type='R'
                    and t2.constraint_type='P'
                    and t2.constraint_name=t1.r_constraint_name
                    and t3.constraint_name=t1.constraint_name
        )
        A
LEFT JOIN
        (
                SELECT
                        T1.constraint_name AS PK,
                        T2.column_name     AS PK_COL
                FROM
                        dba_constraints T1,
                        DBA_CONS_COLUMNS T2
                WHERE
                        T1.CONSTRAINT_TYPE='P'
                    AND T1.constraint_name=T2.constraint_name
        )
        B
ON
        A.PK=B.PK
--DM6获取主键信息
SELECT 
        A.NAME AS CONSTRAINT_NAME,
        B.NAME AS TABLE_NAME,
        E.NAME AS COLUMN_NAME, 
        'P'    AS CONSTRAINT_TYPE 
FROM 
        SYSDBA.SYSTABLES B,
        SYSDBA.SYSCONSTRAINTS A,
        SYSDBA.SYSINDEXES C,
        SYSDBA.SYSINDEXKEYS D,
        SYSDBA.SYSCOLUMNS E 
WHERE 
        A.TABLEID=B.ID 
    AND A.TABLEID=C.TABLEID 
    AND C.ID     =D.ID 
    AND D.COLID  =E.COLID 
    AND A.TABLEID=E.ID 
    AND A.TYPE   ='P' 
    AND A.NAME   =C.NAME 
    AND B.TYPE   ='U' 
--DM6获取主键语句
 select 
        'alter table '||table_name||' add constraint PK_'||replace(table_name,'.','_')||' primary key('||column_name||');' 
 from 
 (  select   
   table_name,
        ltrim(max(sys_connect_by_path(COLUMN_NAME, ',')), ',') as column_name 
 from
    (     select 
                row_number() over(PARTITION by table_name ORDER by COLUMN_NAME) r,
                TABLE_NAME, 
                COLUMN_NAME 
        from 
             ( SELECT 
        u.name||'.'||
        B.NAME AS TABLE_NAME,
        E.NAME AS COLUMN_NAME      
FROM 
        SYSDBA.SYSTABLES B,
        SYSDBA.SYSCONSTRAINTS A,
        SYSDBA.SYSINDEXES C,
        SYSDBA.SYSINDEXKEYS D,
        SYSDBA.SYSCOLUMNS E,
        SYSDBA.SYSSCHEMAS U
WHERE 
        A.TABLEID=B.ID 
    AND A.TABLEID=C.TABLEID 
    AND C.ID     =D.ID 
    AND D.COLID  =E.COLID 
    AND A.TABLEID=E.ID 
    AND A.TYPE   ='P' 
    AND A.NAME   =C.NAME 
    AND B.TYPE   ='U'  
    AND U.SCHID=B.SCHID
order by  TABLE_NAME,d.keyNO ))
 start 
with 
        r=1 CONNECT by prior r =r-1 
    and prior table_name = table_name
group by 
        table_name
order by 
        table_name)
     
--DM6获取外键信息
select  
        fk_tab.OWNER AS FK_OWNER,
        fk_tab.table_name as FK_TABLE,
        fk_tab.constraint_name      as fk,
        fk_tab.col_name        as fk_col,
        PK_TAB.PK_OWNER,
        pk_tab.table_name      as pk_table,
        PK_TAB.CONSTRAINT_NAME, 
        pk_tab.colUMN_name as pk_col 
from 
        ( 
        select 
                P1.OWNER,
                p1.constraint_name,
                p1.table_name,
                p1.col_name,
                P2.NAME AS R_CONSTRAINT_NAME 
        from 
                (
                select 
                        e.id, 
                        a.name as constraint_name, 
                        c.name as table_name, 
                        d.name as col_name, 
                        'R'    as constraint_type ,
                        F.NAME AS OWNER
                from (select name,id,tableid from sysdba.sysindexes where exists 
                                ( 
                                select 
                                        1 
                                from 
                                        sysdba.sysrefconstraints 
                                where 
                                        exists 
                                        ( 
                                        select 
                                                1 
                                        from 
                                                sysdba.sysindexes 
                                        where 
                                                exists 
                                                ( 
                                                select 
                                                        1 
                                                from 
                                                        sysdba.sysconstraints j 
                                                where 
                                                        j.type='P' 
                                                    and j.tableid     >999 
                                                    and name      =sysdba.sysindexes.name 
                                                ) 
                                            and id= sysdba.sysrefconstraints.rid 
                                        ) 
                                    and fid= sysdba.sysindexes.id 
                                )) 
                        a, 
                        sysdba.sysindexkeys b, 
                        sysdba.systables c, 
                        sysdba.syscolumns d, 
                        sysdba.sysconstraints e ,
                        SYSDBA.SYSSCHEMAS F
                where 
                        a.id = b.id 
                    and a.tableid      = c.id 
                    and c.id      = d.id 
                    and b.colid   = d.colid 
                    and c.type    ='U' 
                    and a.name    =e.name 
                    AND C.SCHID=F.SCHID
                ) 
                p1 
        LEFT JOIN 
                ( 
                SELECT 
                        E.NAME, 
                        C.ID 
                FROM 
                        SYSDBA.SYSCONSTRAINTS C, 
                        SYSDBA.SYSREFCONSTRAINTS D, 
                        SYSDBA.SYSINDEXES E 
                WHERE 
                        C.ID=D.ID 
                    AND D.RID   =E.ID 
                ) 
                P2 
        ON 
                P1.ID=P2.ID 
        ) 
        FK_TAB, 
        (
        SELECT 
                F.NAME AS PK_OWNER,
                A.NAME AS CONSTRAINT_NAME,
                B.NAME AS TABLE_NAME,
                E.NAME AS COLUMN_NAME, 
                'P'    AS CONSTRAINT_TYPE 
        FROM 
                SYSDBA.SYSTABLES B,
                SYSDBA.SYSCONSTRAINTS A,
                SYSDBA.SYSINDEXES C,
                SYSDBA.SYSINDEXKEYS D,
                SYSDBA.SYSCOLUMNS E ,
                SYSDBA.SYSSCHEMAS F
        WHERE 
                A.TABLEID=B.ID 
            AND A.TABLEID=C.TABLEID 
            AND C.ID     =D.ID 
            AND D.COLID  =E.COLID 
            AND A.TABLEID=E.ID 
            AND A.TYPE   ='P' 
            AND A.NAME   =C.NAME 
            AND B.TYPE   ='U'
            AND B.SCHID=F.SCHID
        ) 
        PK_TAB 
WHERE 
        FK_TAB.R_CONSTRAINT_NAME=PK_TAB.CONSTRAINT_NAME