3

How to construct an INSERT statement so that it would not generate the error "insert or update on table ... violates foreign key constraint" in case if the foreign key value does not exist in the reference table?

I just need no record created in this case and success response.

Thank you

spoonboy
  • 2,570
  • 5
  • 32
  • 56
  • 2
    There's a [technique described here](https://stackoverflow.com/a/36039580/314291) which joins back to the FK tables during the insert, thus dropping rows which have invalid referential integrity – StuartLC Jul 21 '17 at 04:57

2 Answers2

1

You could create a function with plpgsql, which inserts a row and catches the exception:

CREATE FUNCTION customInsert(int,varchar) RETURNS VOID
AS $$ 
BEGIN
INSERT INTO foo VALUES ($1,$2);
EXCEPTION
WHEN foreign_key_violation THEN --do nothing
END;
$$ LANGUAGE plpgsql

You can then call this function by this:

SELECT customInsert(1,'hello');

This function tries to insert the given parameters into the table foo and catches the foreign_key_violation error if occurs.

Of course you can generalise the function more, to be able to insert in more than one table, but your question sounded like this was only needed for one specific table.

Sebastian Walla
  • 1,104
  • 1
  • 9
  • 23
1

Use a query as the source for the INSERT statement:

insert into the_table (id, some_data, some_fk_column
select *
from ( 
     values (42, 'foobar', 100)
) as x(id, some_data, some_fk_column)
where exists (select *
              from referenced_table rt
              where rt.primary_key_column = x.some_fk_column);

This can also be extended to a multi-row insert:

insert into the_table (id, some_data, some_fk_column
select *
from ( 
     values 
      (42, 'foobar', 100),
      (24, 'barfoo', 101)
) as x(id, some_data, some_fk_column)
where exists (select *
              from referenced_table rt
              where rt.primary_key_column = x.some_fk_column);

You didn't show us your table definitions so I had to make up the table and column names. You will have to translate that to your names.