1

(There are similar sounding questions out there at first glance, but none any that are actually identical from what I've seen so far)

I'm on PostgreSQL 9.4.7

In narrow terms of my use case (good context, but high clutter):

  • In my database we have a filesystem (the tree of files and folders is a self referencing table) called "filesystementity". Hundreds of different tables have foreign key constraints to this filesystementity table. Now, one of these files is a file of code. Anything that uses this file may depend on that code. Now say I want to alter or delete the code in that file. I need to find the uses of the file so I can check if anything using it needs to be upgraded or fixed. Maybe not all hundred or so tables referencing filesystementity fit this use case, but suppose maybe some dozens do, and I don't know which. What I want is to find out which tables use a particular file (a particular table row), through these foreign key constraints. Ideally I'd like to check all foreign key constraints that point to this filesystementity table for uses of this particular row. What would be not ideal is querying every possible relation manually, since their are hundreds of uses of this table.

In more generalized terms (low context, but better focus on simple question)

  • I want to see (or find, or query, or whatever) foreign key constraint usages targeting a specific row in a table, with as minimal effort as possible, even when there are hundreds of possible tables that might use that row from that table.

Table example (abbreviated output of \d filesystementity in psql):

                   Table "public.filesystementity"
      Column      │            Type             │     Modifiers      
──────────────────┼─────────────────────────────┼────────────────────
 type             │ character varying(31)       │ not null
 id               │ bigint                      │ not null
 name             │ character varying(255)      │ not null
 lastmodifiedtime │ timestamp without time zone │ not null
Indexes:
    "filesystementity_pkey" PRIMARY KEY, btree (id))
Referenced by:
    TABLE "simplefileprovider" CONSTRAINT "fk12af6a7fb389331e" FOREIGN KEY (file_id) REFERENCES filesystementity(id)
    TABLE "componentfactory" CONSTRAINT "fk1eeac96d37153871" FOREIGN KEY (springfile_id) REFERENCES filesystementity(id)
    TABLE "reportresult_filesystementity" CONSTRAINT "fk306669cbab60843" FOREIGN KEY (files_id) REFERENCES filesystementity(id)
    TABLE "componentfactory_filesystementity" CONSTRAINT "fk32bcd74035cf7051" FOREIGN KEY (dependencies_id) REFERENCES filesystementity(id)
    TABLE "animalimage" CONSTRAINT "fk361664a1b389331e" FOREIGN KEY (file_id) REFERENCES filesystementity(id)
    TABLE "dnabulkuploadfile" CONSTRAINT "fk398d3f28b389331e" FOREIGN KEY (file_id) REFERENCES filesystementity(id)
    TABLE "picture" CONSTRAINT "fk40c8f4de8d1e20ff" FOREIGN KEY (large_id) REFERENCES filesystementity(id)
    TABLE "picture" CONSTRAINT "fk40c8f4deb74261e7" FOREIGN KEY (smallalternate_id) REFERENCES filesystementity(id)
    etc
    etc
    etc

In the above example table, I have a filesystementity id, and want to find all tables (in the "Referenced by" section) that refer to that id.

Some possible solutions right now, except not convenient enough or doesn't quite feel right (I am seeking a simple and elegant solution if possible):

  • begin transaction; delete row; check for foreign key violations; rollback
    • I don't like this because it doesn't seem wise to play around with transactions like that, and also will it even work if some relations cascade delete? I don't suspect so
  • Manually select from every table that refers to filesystementity, seeking usages of the target row.
    • This is not really conveniently feasible when there are hundreds of such foreign key constraints.

Since whenever you delete a row, obviously the database checks constraints, I figure there must be some mechanism to automatically query for constraints that use a row.... I just do not know if such a mechanism is exposed to the user in psql.

There are many questions asking how to find foreign key constraints on a table. These are the questions that sound similar at first glance, but are actually different. I know the foreign key constraints on a table (see output above), I just want to find which foreign key constraints are actually in use for a particular row.

Your Answer

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

Browse other questions tagged or ask your own question.