Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION

On Tue, Jul 6, 2021 at 3:29 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17085
> Logged by: Victor Porton
> Email address: porton(at)narod(dot)ru
> PostgreSQL version: 13.3
> Operating system: Linux
> Description:
>
> When there is either ON DELETE NO ACTION or ON UPDATE NO ACTION for a
> foreign key, referential integrity is not preserved anyway.
>

Yes, it is preserved, the delete or update is prevented.

> Therefore in this case ALTER TABLE should be able to create an index

without referential checking.
>

Indexes are single table only and thus do not care about referential
integrity or perform any referential checking.

>
> It is especially useful for removing superfluous data like:
>
> delete from tags using transactions where not exists(select * from
> transactions where tags.tx_id=transactions.id);
>
> I've got into a trouble: This command runs too long because of no foreign
> key, but I can't create a foreign key because this command didn't run yet
> to
> make referntial integrity working.
>
>
Foreign keys likewise don't affect performance...they are strictly
concerned with data updates. Now, a FK column is not indexed by default so
depending on how queries are written, and the data in for the FK, there may
be a performance gain from adding an index.

All that is to say if you want to turn a non-FK column into an FK you may
find it helpful to add the index first, fix the data, then add the FK.

David J.