将pandas dataframe插入表时由于外键而出错

Question

我正在执行一个非常简单的操作，从一个表中获取值，然后在Postgres中将其加载到空表中。在

第一个表的结构如下：

CREATE TABLE public.entities
(
  canonical character varying(255) NOT NULL DEFAULT ''::character varying,
  db_id character varying(255) NOT NULL,
  code character varying(255),
  description character varying(4096),
  level character varying(255),
  class character varying(255) NOT NULL,
  model character varying(255) NOT NULL DEFAULT 'test'::character varying,
  variants text[],
  CONSTRAINT entities_pkey PRIMARY KEY (db_id, canonical, class, model)
)

我试图加载已处理数据的第二个表具有以下结构

^{pr2}$

现在，当我试图插入（行或整个数据帧）时，我收到完整性错误：

IntegrityError: insert or update on table "entities_variants" violates foreign key constraint "fk8g9miq5xcx1jtp85lysrkjhm" DETAIL: Key (entitymodel_canonical, entitymodel_db_id, entitymodel_model, entitymodel_class)=(Timothy Dress, 92002, ofirmev, Name) is not present in table "entities".

我已经确认第一个表中确实存在这个数据。在

Answer 1

定义外键的列顺序与引用列的顺序不匹配。在

您有entitymodel_canonical引用db_id，entitymodel_db_id引用{}等

我认为你的约束声明应该是：

CONSTRAINT fk8g9miq5xcx1jtp85lysrkjhm FOREIGN KEY (entitymodel_db_id ,entitymodel_canonical, entitymodel_class, entitymodel_model)
  REFERENCES public.entities (db_id, canonical, class, model) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION