今天更新兩個SQL。是用來查詢PG中，主表被子表引用的外鍵，或子表引用了哪個主表的主鍵。
廢話不多說，直接上實驗！
CentOS 7 + PG 10
創(chuàng)建兩個實驗表，test01為主表，test02為子表，test02引用test01中的id列。
test=# create table test01( test(# id int primary key, test(# col1 varchar(20) test(# ); CREATE TABLE test=# create table test02( test(# id int primary key, test(# test01_id int references test01(id), test(# col1 varchar(20) test(# ); CREATE TABLE
插入數(shù)據(jù)
test=# insert into test01 values (1, 'a'); INSERT 0 1 test=# insert into test01 values (2, 'b'); INSERT 0 1 test=# insert into test01 values (3, 'c'); INSERT 0 1 test=# insert into test02 values (1, 1, 'a'); INSERT 0 1 test=# insert into test02 values (2, 1, 'a'); INSERT 0 1 test=# insert into test02 values (3, 1, 'a'); INSERT 0 1 test=# insert into test02 values (4, 2, 'b'); INSERT 0 1 test=# insert into test02 values (5, 2, 'b'); INSERT 0 1 test=# insert into test02 values (6, 11, 'b'); ERROR: insert or update on table "test02" violates foreign key constraint "test02_test01_id_fkey" DETAIL: Key (test01_id)=(11) is not present in table "test01".
查詢主表被哪個子表引用。如果結(jié)果為空，說明沒有任何子表引用的該表。
test=# SELECT tc.constraint_name, tc.table_name, # 子表 kcu.column_name, ccu.table_name AS foreign_table_name, # 主表 ccu.column_name AS foreign_column_name, tc.is_deferrable, tc.initially_deferred FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name where constraint_type = 'FOREIGN KEY' AND ccu.table_name='test01'; # 輸入主表 constraint_name | table_name | column_name | foreign_table_name | foreign_column_name | is_deferrable | initially_deferred -----------------------+------------+-------------+--------------------+---------------------+---------------+-------------------- test02_test01_id_fkey | test02 | test01_id | test01 | id | NO | NO (1 row)
查詢子表引用的哪個主表。如果結(jié)果為空，說明沒有任何引用主表。
test=# SELECT tc.constraint_name, tc.table_name, # 子表 kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name, # 主表 tc.is_deferrable, tc.initially_deferred FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='test02'; # 輸入子表 constraint_name | table_name | column_name | foreign_table_name | foreign_column_name | is_deferrable | initially_deferred -----------------------+------------+-------------+--------------------+---------------------+---------------+-------------------- test02_test01_id_fkey | test02 | test01_id | test01 | id | NO | NO (1 row)
補(bǔ)充：PostgreSQL 外鍵引用查詢
根據(jù)一個表名，查詢所有外鍵引用它的表，以及那些外鍵的列名
key_column_usage（系統(tǒng)列信息表），
pg_constraint（系統(tǒng)所有約束表）
SELECT x.table_name,
x.column_name
FROM information_schema.key_column_usage x
INNER JOIN (SELECT t.relname,
a.conname
FROM pg_constraint a
INNER JOIN pg_class ft
ON ft.oid = a.confrelid
INNER JOIN pg_class t
ON t.oid = a.conrelid
WHERE a.contype = 'f'
AND a.confrelid =
(select e.oid
from pg_class e
where e.relname = 'xxx_table')
) tp
ON (x.table_name = tp.relname AND
x.constraint_name = tp.conname)
示例：
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
標(biāo)簽：榆林 海西 濮陽 寶雞 溫州 辛集 杭州 昭通
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Postgresql 查詢表引用或被引用的外鍵操作》，本文關(guān)鍵詞 Postgresql,查詢表,引用,或,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
