主页 / dba / 问题 / 341568
Accepted
Evan Carroll
Asked: 2024-08-12 09:43:12 +0800 CST

如何找到所有具有特定名称的列上拟议外键的所有违规行为？

  • 772

假设我有多个包含列的表foobar。我希望使所有这些列都引用foobar.id为FOREIGN KEY。问题是我知道从我导入数据的一方那里存在此架构违规行为。但是，我宁愿不迭代地找到它们。

我如何才能找到所有违规行为，以便为第三方准备数据完整性问题报告？

postgresql

1 个回答

  1. Best Answer

    使用\gexec

    此查询将生成一个我们稍后可以执行的查询。如果我们使用，psql我们可以使用\gexec执行最后一个查询的返回——这是专门为这类东西制作的功能，

    WITH pkey(table_catalog, table_schema, table_name, column_name)
    AS ( VALUES ('db', 'schema', 'table', 'pkey') ),
fkey(column_name) AS ( VALUES ('table_fkey') )
SELECT FORMAT(
    $$
    SELECT DISTINCT %I
    FROM ( %s ) AS v(%I)
    WHERE NOT EXISTS (
        SELECT 1
        FROM %I.%I.%I AS p
        WHERE p.%I = v.%I
    )
    $$,
    pkey.column_name,
    string_agg( sql.stmt, 'UNION ALL'),
    pkey.column_name,
    pkey.table_catalog, pkey.table_schema, pkey.table_name,
    pkey.column_name,
    pkey.column_name

)
FROM (
    SELECT FORMAT(
        $$
        SELECT %I FROM %I.%I.%I
        $$,
        c.column_name, c.table_catalog, c.table_schema, c.table_name
    )
    FROM information_schema.columns AS c
    INNER JOIN fkey USING ( column_name )
) AS sql(stmt)
CROSS JOIN pkey
GROUP BY pkey.table_catalog, pkey.table_schema, pkey.table_name, pkey.column_name;

    假设，

    WITH pkey(table_catalog, table_schema, table_name, column_name)
    AS ( VALUES ('tec', 'tec', 'codes_schedule', 'schedule_id') ),
fkey(column_name) AS ( VALUES ('schedformtypecd') )
...

    它将产生如下查询，

    SELECT DISTINCT schedule_id                                   
FROM (                                                        
        SELECT schedformtypecd FROM tec.tec.c_contributiondata
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_creditdata      
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_debtdata        
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_expenddata      
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_expendrepayment 
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_loandata        
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_pledgedata      
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_traveldata      
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_assetdata       
        UNION ALL                                             
        SELECT schedformtypecd FROM tec.tec.c_candidatedata   
         ) AS v(schedule_id)                                  
WHERE NOT EXISTS (                                            
        SELECT 1                                              
        FROM tec.tec.codes_schedule AS p                      
        WHERE p.schedule_id = v.schedule_id                   
)                                                             
AND schedule_id is NOT NULL

    运行后将\gexec输出

     schedule_id 
-------------
 A
 B2
 M
 t-ctr
(4 rows)

    这是数据库中所有阻止您在上创建外键的schedformtypecd异常tec.tec.codes_schedule.schedule_id

    示例取自db-Texas-Ethics-Commission

    • 0

相关问题