Wyszukiwanie kluczy obcych powiązanych z kolumną w tabeli w bazie danych PostgreSQL
Czasami potrzebne jest wyszukiwanie wszystkich tabel i ich kolumn powiązanych kluczami obcymi z kolumną w tabeli źródłowej. W przypadku bazy danych PostgreSQL można to zrobić wykorzystując information_schema.
Taka możliwość przydaje się jeśli musimy np. przepiąć wszystkie dane powiązane do wiersza na inny wiersz tej samej tabeli, a są do niego powiązane dane poprzez klucz obcy. Przykładowo załóżmy, że mamy CRM i w nim tabelę klient o automatycznie numerowanym kluczu głównym założonym na kolumnie o nazwie id. Do klucza głównego tej tabeli istnieją klucze obce w dużej części systemu, a okazało się, że przez nie uwagę pracownicy stworzyli dwa razy tego samego klienta. Po dwóch miesiącach błąd został wykryty i należy przeciąć na klienta A (434) wszystkie dane dodatkowe z klienta B (222) np. korespondencja, dane finansowe itp. Wykorzystując poniższe zapytanie można wyszukać wszystkie tabele i kolumny na których należy wykonać update zmieniając identyfikatory kluczy obcych np.
select f_kcu.table_name, f_kcu.column_name from information_schema.key_column_usage as p_kcu join information_schema.constraint_column_usage f_ccu on p_kcu.table_name = f_ccu.table_name and p_kcu.column_name = f_ccu.column_name join information_schema.key_column_usage f_kcu on f_ccu.constraint_name = f_kcu.constraint_name where p_kcu.table_name = 'klient' and p_kcu.column_name = 'id' and p_kcu.constraint_name <> f_kcu.constraint_name order by f_kcu.table_name;
Przykładowe zapytanie do aktualizacji wierszy z tabelach powiązanych.
UPDATE wiadomosci SET client_id = 222 WHERE client_id = 434;