1

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 ?

  • Unclear. If a record appears in a Table2 or Table3, then the [same] Foreign Key value must appear in Table1, assuming your constraints are working properly. – Phill W. Mar 27 at 11:19
  • Is your question: Given a value in table_1, in which of table_2 OR table_3 does it appear? Or maybe also neither the two? – Vérace Mar 27 at 12:13
2
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 table1 and table3, use AND.

When you need ids which are present at least in one of these tables, use OR.

1

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 IN predicate:

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 INTERSECT and UNION:

(
  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.