How to get number of PK used as FK?
Table 1 has a column ID (PK). Table 2 & 3 has a column ID (FK - refers Table 1 PK).
How to get Value 1 (PK) has used in table 2 & 3 or not without referring table 2 or 3 ?
SELECT id FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id) AND /* OR */ EXISTS (SELECT 1 FROM table3 WHERE table1.id = table3.id)
When you need
ids which are present both in
When you need
ids which are present at least in one of these tables, use
You cannot determine which keys are referenced and which are not without looking up the referencing tables (tables 2 and 3 in your example). There is just no such information stored in the referenced table's data or metadata. The mere fact that you have declared a foreign key from one table to another does not, unfortunately, give you the ability to extract this kind of information without hitting the referencing table.
You can use Akina's suggestion as one way to find the keys that are being referenced. You can also use the
SELECT id FROM table1 WHERE id IN (SELECT id FROM table2) OR id IN (SELECT id FROM table3) OR ... /* repeat as many times as there are referencing tables */
And here is another method, using set operators
( SELECT id FROM table1 INTERSECT SELECT id FROM table2 ) UNION ( SELECT id FROM table1 INTERSECT SELECT id FROM table3 ) UNION ... /* repeat as many times as there are referencing tables */
A small note regarding the last query:
INTERSECT has higher precedence than
UNION in PostgreSQL, which means the above query would work the same without the brackets. Still, using the brackets makes your intention clearer, which is never a bad thing in query writing.