๐™ณ๐™ฐ๐šƒ๐™ฐ๐™ฑ๐™ฐ๐š‚๐™ด/๐™ฟ๐š˜๐šœ๐š๐š๐š›๐šŽ๐š‚๐š€๐™ป

[PostgreSQL] PostgreSQL์—์„œ ํŠน์ • ํ…Œ์ด๋ธ”์ด ์ฐธ์กฐํ•˜๋Š” ์™ธ๋ž˜ ํ‚ค ์ด๋ฆ„ ํ™•์ธํ•˜๊ธฐ

ํ•ด๋ฒ„๋‹ˆ 2025. 4. 28. 18:59
๋ฐ˜์‘ํ˜•

 

ํŠน์ • ํ…Œ์ด๋ธ”์ด ์ฐธ์กฐํ•˜๋Š” ์™ธ๋ž˜ ํ‚ค ๊ด€๊ณ„๋ฅผ ํ™•์ธํ•˜๋Š” ์ฟผ๋ฆฌ

SELECT conrelid::regclass AS table_name, 
       conname AS constraint_name, 
       confrelid::regclass AS referenced_table
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'your_table_name'::regclass;

 

[conrelid]

์ œ์•ฝ์ด ๊ฑธ๋ ค ์žˆ๋Š” ํ…Œ์ด๋ธ”์˜ OID(Object Identifier)์ด๋‹ค.

conrelid::regclass๋กœ ํ•ด๋‹น ํ…Œ์ด๋ธ” ์ด๋ฆ„์„ ์กฐํšŒํ•  ์ˆ˜ ์žˆ๋‹ค.

 

[confrelid]

์™ธ๋ž˜ ํ‚ค๊ฐ€ ์ฐธ์กฐํ•˜๋Š” ๋Œ€์ƒ ํ…Œ์ด๋ธ”์˜ OID์ด๋‹ค.

confrelid::regclass๋กœ ์ฐธ์กฐ ๋Œ€์ƒ ํ…Œ์ด๋ธ” ์ด๋ฆ„์„ ์กฐํšŒํ•  ์ˆ˜ ์žˆ๋‹ค.

 

[pg_constraint]

PostgreSQL์—์„œ ํ…Œ์ด๋ธ” ๊ฐ„์˜ ์ œ์•ฝ ์กฐ๊ฑด(Constraint)์„ ๊ด€๋ฆฌํ•˜๋Š” ์‹œ์Šคํ…œ ์นดํƒˆ๋กœ๊ทธ ํ…Œ์ด๋ธ”์ด๋‹ค.

์™ธ๋ž˜ ํ‚ค, ๊ธฐ๋ณธ ํ‚ค, ๊ณ ์œ  ํ‚ค, ์ฒดํฌ ์ œ์•ฝ ๋“ฑ์„ ์ €์žฅํ•˜๊ณ  ์žˆ๋‹ค.

 

[contype = 'f']

contype์€ ์ œ์•ฝ์˜ ์œ ํ˜•์„ ๋‚˜ํƒ€๋‚ด๋Š”๋ฐ, 'f'๋Š” ์™ธ๋ž˜ ํ‚ค(Foreign Key)๋ฅผ ์˜๋ฏธํ•œ๋‹ค.

๋”ฐ๋ผ์„œ ์™ธ๋ž˜ ํ‚ค ์ œ์•ฝ ์กฐ๊ฑด๋งŒ ์กฐํšŒํ•œ๋‹ค.

 

['your_table_name'::regclass]

์—ฌ๊ธฐ์„œ๋Š” your_table_name์„ ์‹ค์ œ ํ…Œ์ด๋ธ” ์ด๋ฆ„์œผ๋กœ ๋ฐ”๊พธ์–ด ์‚ฌ์šฉํ•œ๋‹ค.

์ด ๋ถ€๋ถ„์€ ์ฟผ๋ฆฌ์—์„œ conrelid๋ฅผ ํ•ด๋‹น ํ…Œ์ด๋ธ”์˜ OID๋กœ ๋น„๊ตํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค.

 

 

 

์˜ˆ์ œ SQL

SELECT conrelid::regclass AS table_name, 
       conname AS constraint_name, 
       confrelid::regclass AS referenced_table 
FROM pg_constraint 
WHERE contype = 'f' AND conrelid = 'orders'::regclass;

 

 

์‹คํ–‰ ๊ฒฐ๊ณผ ์˜ˆ์‹œ

table_name | constraint_name          | referenced_table 
-------------------------------------------------------
orders     | orders_user_id_fkey      | users
orders     | orders_product_id_fkey   | products

 

 

 

 

 

 

๋ฐ˜์‘ํ˜•
๋Œ“๊ธ€์ˆ˜0