Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
Date
Msg-id CAKFQuwbiU1kswK7fkKf9k=qT-_hTwhbNyWOoy-s_1j55cx=Z7w@mail.gmail.com
Whole thread Raw
In response to BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION  (PG Bug reporting form <noreply@postgresql.org>)
Responses Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
List pgsql-bugs
Tree view
On Tue, Jul 6, 2021 at 3:29 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17085
Logged by:          Victor Porton
Email address:      porton@narod.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.

pgsql-bugs by date:

Previous
From: PG Bug reporting form
Date:
Subject: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION
Next
From: Pantelis Theodosiou
Date:
Subject: Re: BUG #17085: Should be able to create an index without referential checking when ON DELETE NO ACTION