Início / dba / Perguntas / 341568
Accepted
Evan Carroll
Asked: 2024-08-12 09:43:12 +0800 CST

Como posso encontrar todas as violações de uma chave estrangeira proposta em todas as colunas com um nome específico?

  • 772

Digamos que eu tenha várias tabelas com uma coluna foobar. E eu desejo fazer com que todas essas colunas sejam referenciadas foobar.idcomo um arquivo FOREIGN KEY. O problema é que sei que há violações nesse esquema por parte da parte de quem estou importando os dados. Mas prefiro não encontrá-los todos iterativamente.

Como posso encontrar todas as violações para preparar um relatório de questões de integridade de dados para terceiros?

postgresql

1 respostas

  1. Best Answer

    Usando\gexec

    Esta consulta irá gerar uma consulta que poderemos executar posteriormente. Se estivermos usando psqlpodemos usar \gexecpara executar o retorno da última consulta - um recurso feito especificamente para esse tipo de coisa,

    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;

    Dado algo como,

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

    Ele produzirá uma consulta como esta,

    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

    Que depois de executado \gexecirá gerar

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

    Quais são todas as exceções no banco de dados que impediriam você de criar uma chave estrangeira geral schedformtypecdparatec.tec.codes_schedule.schedule_id

    Exemplo retirado de db-Texas-Ethics-Commission

    • 0

relate perguntas