Reputation: 489
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
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
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