Re: Feature Request: Better handling of foreign keys in DELETE statements

Hello

2011/12/19 Daniel Migowski <dmigowski(at)ikoffice(dot)de>:
> Hi,
>
>
>
> I face the following problem: I have a large table with 12 million
> addresses, referenced by 20 other tables (some containing about one million
> entries). There are indexes on the foreign keys.
>
>
>
> Now I wanted to delete about 10 million addresses (that are not referenced
> anymore from anywhere), and have a statement like:
>

you can disable check per session if you need

ALTER TABLE ... DISABLE TRIGGER ALL;

Regards

Pavel Stehule

>
>
> DELETE FROM address
>
> WHERE id NOT IN (SELECT address_id FROM bank where address_id IS NOT NULL)
>
>    AND id NOT IN (SELECT poboxaddress_id FROM bank where poboxaddress_id IS
> NOT NULL)
>
>    AND id NOT IN (SELECT address_id FROM bankconnection where address_id IS
> NOT NULL)
>
> ...lots more...
>
>
>
> This takes more than 10 hours here (I had to cancel the statement).
>
>
>
> I have two suggestions:
>
>
>
> 1.       Currently for each row to be deleted, a SELECT is done in each
> column referencing the deleted entry. This takes really a lot of time. It is
> possible to check in an elegant way if an entry can be deleted, like in the
> above query.  I know it is not easy to autocreate such a statement, but this
> would make deletions much faster.
>
> 2.       I would have loved a special option “UNREREFENCED” given to the
> delete statement, so all rows referenced from anywhere would automagically
> be excluded from my delete statement. When this keyword is given, no FK
> checks have to be done, because FK referenciality cannot be violated anyway.
>
>
>
> DELETE UNREFERENCED FROM address WHERE …;
>
>
>
> Thanks for your time and this great database product.
>
>
>
> Regards,
>
> Daniel Migowski