Re: Getting the list of foreign keys (for deleting data from the database)

Mario Splivalo schrieb am 02.08.2015 um 17:04:
> Suppose I have a table_detail that has column table_id which is FK
> pointing to table(id), I would need to do something like this:
>
> SELECT foo_drop_all_constraints();
> SELECT * FROM table INTO table_copy WHERE date_created >= '2015-01-01';
> SELECT table_detail.* INTO table_detail_copy FROM table_detail JOIN
> table_copy ON table_detail.table_id = table_copy.id
> DROP TABLE table;
> DROP TABLE table_copy
> ALTER TABLE table_copy RENAME TO table;
> ALTER TABLE table_detail_copy TO table_detail;
> SELECT foo_restore_all_constraints();
>
> Now, what am I asking is - is there a tool which would help me find all
> the _detail tables? I know I could query pg_constraints and similar
> views but before I go onto hacking into those I'm wondering if there is
> something that could aid me in doing so.

The SQL tool I maintain (http:://www.sql-workbench.net) has such a feature.

It supports a (SQL Workbench specific) command that generates (recursively) the delete
statements starting with the "root" table given a condition on the root table:

http://www.sql-workbench.net/manual/wb-commands.html#command-gendelete

The generated SQL script honors the FKs and thus there is no need to drop all constraints.

In your case it would be something like:

WbGenerateDelete -table=root_table -columnValue="date_created >= '2015-01-01'";

The output is a script with the DELETEs in the right order - or at least it _should_.

I have to admit that I had to deal with one or two really large schemas (> 700 tables) where the
delete statements where not ordered properly, especially if there are multiple FKs to/from the
same table.

Note that the generated statements are not pretty and far from being efficient.

> Of course, if this is not the best approach I'd appreciate different
> views/opinions.

In my experience, setting all the FKs to "on delete cascade" and properly indexing the FK
columns is very often faster than doing the deletes all "manually".

Another option (if you need to do that very often) is to partition the tables by e.g. year.
Then getting rid of all the data for a year is as simple as dropping the partitions for that year.

However partitioning and foreign key constraints don't work together in Postgres, which is a real shame.

Thomas