How to make ON CONFLICT work for compound foreign key columns?

5 votes

2 answers

11010 views

                          I'm having trouble with ON CONFLICT not working for foreign key columns where the foreign key is compound.   Here's an example.

    create table foreign_table (
       id_a text    not null,
       id_b text    not null,
       id   integer primary key,
       constraint ft_a_b_key unique (id_a, id_b)
    );

    create table my_table (
       id          integer,
       ftable_id_a text,
       ftable_id_b text,
       constraint my_table_a_b_fk
          foreign key (ftable_id_a, ftable_id_b) references foreign_table (id_a, id_b)
    );

Using this query:

    insert into tcell_test.my_table (id, ftable_id_a, ftable_id_b) 
        values (3, 'a3', 'b3') on conflict do nothing ;

where, say, 'a3' isn't in foreign_table, I would expect the ON CONFLICT to handle the error.

Instead I get the error:

>      ERROR: insert or update on table "my_table" 
>        violates foreign key constraint "my_table_a_b_fk" 
>     Detail: Key (ftable_id_a, ftable_id_b)=(a3, b3) 
>        is not present in table "foreign_table".

Is there a way to correct this so ON CONFLICT handles the error?

                        

Asked by Paul C (153 rep)

Nov 8, 2019, 10:49 PM
Last activity: Nov 12, 2019, 01:51 PM

How to make ON CONFLICT work for compound foreign key columns?

Related Questions