在插入查询中不处理外键冲突SQL

Question

在迁移程序中，我对表_1执行插入查询，其定义如下：

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;

现在，当我尝试插入此表时，我得到以下错误：

事务错误中的错误：在表"table_1"上插入或更新违反外键约束"table_2_fkey"

我尝试在以下表单中执行插入查询：

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;

有没有办法防止违反外键约束（跳过它们）？

类似on违规什么都不做。

注意：它不违反第二个外键约束，因为我将此表（table_2）中的数据填充为我试图填充到table_1的行（用于规范化）。table_2是一个早先存在的表，我在另一个程序中填充了那里的行，所以那里可能有丢失的数据，我想跳过插入table_1不匹配外键table_2的行。

使用PostgreSQL 10.16，由Visual C build 1800编译，64位

Answer 1

我使用了这个答案中查询的变体。感谢@_horse_with_no_name

这是对我有用的查询：

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;

Answer 2

没有违反约束的“什么都不做”选项。您可以在表_2中创建一个虚拟字段，以便在父表中无法匹配该值时，该值可以在表_2中引用（排序占位符值）。

或者，您可以使用映射表来删除外键约束。这样，您可以决定映射表是否应该插入新记录。这不太可能是满足您设计目标的最佳方式。

table_1
    id
    ...  other data fields ...

table_2
    id
    ...  other data fields ...

map_table
    id
    table1_id (FK)
    table2_id (FK)