CODEWITHSUNDEEP
sqlpostgresql
Wahalez
Wahalez

Reputation: 489

Do nothing on foreign key violation in SQL insert query

In a migration program, I execute an insert query to table_1 which is defined like this :

CREATE TABLE table_1
(
    col1 character varying(4)  COLLATE pg_catalog."default" NOT NULL,
    col2 character varying(10) COLLATE pg_catalog."default" NOT NULL,
    col3 character varying(10) COLLATE pg_catalog."default" NOT NULL,
    col4 character varying(40) COLLATE pg_catalog."default" NOT NULL,
    col5 date NOT NULL,
    col6 date NOT NULL,

    CONSTRAINT table_1_pkey 
        PRIMARY KEY (col1, col2, col3, col4, col5, col6),

    CONSTRAINT table_2_fkey FOREIGN KEY (col4)
        REFERENCES table_2 (col1) MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION,
    CONSTRAINT table_3_fkey FOREIGN KEY (col3)
        REFERENCES table_3(col1) MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION
            NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Now when I try to insert to this table, I get this error :

Error in transaction error: insert or update on table "table_1" violates foreign key constraint "table_2_fkey"

I tried to do an insert query in this form :

INSERT INTO table_1(col1, col2, col3, col4, col5, col6) 
VALUES 
(col1_value1, col2_value1, col3_value1, col4_value1, col5_value1, col6_value1),
(col1_value2, col2_value2, col3_value2, col4_value2, col5_value2, col6_value2),
(col1_value3, col2_value3, col3_value3, col4_value3, col5_value3, col6_value3)
ON CONFLICT (col1, col2, col3, col4, col5, col6) DO NOTHING;

Is there a way to prevent violation of the foreign key constraint (skip them)?

Something like ON VIOLATION DO NOTHING.

Note: it doesn't violate the second foreign key constraint because I fill the data in this table (table_2) with the rows I try to fill into table_1 (for normalization). table_2 is a table that existed earlier and I fill the rows there in another program, so there might be missing data there, and I want to skip inserting the rows to table_1 that doesn't match the foreign key to table_2.

Using PostgreSQL 10.16, compiled by Visual C++ build 1800, 64-bit

Upvotes: 0

Views: 5137

Answers (2)

Wahalez
Wahalez

Reputation: 489

I used a variation of a query from this answer. Thanks to @_horse_with_no_name

This is the query that worked for me :

with data_table(col1, col2, col3, col4,
                col5, col6) as (
    values
        ('value1','','1','num1','1900-01-01'::date, '9999-12-31'::date),
        ('value2','','1','num2','1900-01-01'::date, '9999-12-31'::date)   
)
insert into table_1(col1, col2, col3,
                    col4, col5, col6)
select d.col1, d.col2, d.col3, d.col4,
       d.col5, d.col6
from data_table d
where exists (select 1
              from table_2 t2
              where t2.col1 = d.col4)
on conflict(col1, col2, col3, col4, col5, col6) do nothing;

Upvotes: 2

MichaelD
MichaelD

Reputation: 1326

There is no, "Do Nothing" option for violating constraints. You can create a dummy field in your table_2 such that when the value is unavailable to be matched in the parent table it has something to reference in table_2 (a placeholder value of sorts).

Alternatively, you can remove the foreign key constraint by using a mapping table instead. This way, you can decide if the map table should have a new record inserted. This is unlikely to be best way to meet your design goals.

table_1
    id
    ...  other data fields ...

table_2
    id
    ...  other data fields ...

map_table
    id
    table1_id (FK)
    table2_id (FK)

Upvotes: 1

Related Questions