Re: Rows violating Foreign key constraint exists

On 11/28/19 5:25 AM, Nandakumar M wrote:
> Hi,
> 
> I am using PG version 10.5.
> 
> Saw a table where we have foreign key defined but few thousand rows
> violate the foreign key constraint.

Have you verified that the FK is not in the parent table and is just not 
some index error/corruption?


> It might be useful to have an option to check integrity of any FK (not
> just ones created with NOT VALID clause). Please let me know if there
> is already any way to do this.

Assuming no corruption, how about?:

select distinct on(fk_field) fk_field from child_table where 
child_table.fk_field not in (select parent_field from parent_table);

> 
> Also, is there any way to make sure the FK checking trigger can never
> be disabled (so that such a case will never arise)?

Not sure that can happen as it is baked into existing code. For instance:

https://www.postgresql.org/docs/11/app-pgdump.html

"
--disable-triggers

     This option is relevant only when creating a data-only dump. It 
instructs pg_dump to include commands to temporarily disable triggers on 
the target tables while the data is reloaded. Use this if you have 
referential integrity checks or other triggers on the tables that you do 
not want to invoke during data reload.

...

"
> 
> How do I proceed from here - Do I just delete the inconsistent rows or
> is there something more I have to do?
> 
> Thanks for your help.
> 
> Regards,
> Nanda
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com