我有一个表，其中有一个variant(12)字符字段，这是它的PRIMARY KEY。我跑了这个查询
SELECT * FROM bg WHERE bg_id ='470370111002'
它从表中选择一行。一切看起来不错。然后，我尝试。
INSERT INTO csapp_center_bgs(bg_id,center_id) VALUES('470370111002',2)
在bg_id上有一个外键，看起来像...
ALTER TABLE csapp_center_bgs
ADD CONSTRAINT csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id
FOREIGN KEY (bg_id)
REFERENCES tiger.bg (bg_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
这是确切的错误...
ERROR: insert or update on table "csapp_center_bgs" violates foreign key constraint "csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id"
DETAIL: Key (bg_id)=(470370111002) is not present in table "bg".
********** Error **********
ERROR: insert or update on table "csapp_center_bgs" violates foreign key constraint "csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id"
SQL state: 23503
Detail: Key (bg_id)=(470370111002) is not present in table "bg".
为什么这不起作用？有任何想法吗？这是\d + bg ...
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------+--------------------------------------------------+----------+--------------+-------------
gid | integer | not null default nextval('bg_gid_seq'::regclass) | plain | |
statefp | character varying(2) | | extended | |
countyfp | character varying(3) | | extended | |
tractce | character varying(6) | | extended | |
blkgrpce | character varying(1) | | extended | |
bg_id | character varying(12) | not null | extended | |
namelsad | character varying(13) | | extended | |
mtfcc | character varying(5) | | extended | |
funcstat | character varying(1) | | extended | |
aland | double precision | | plain | |
awater | double precision | | plain | |
intptlat | character varying(11) | | extended | |
intptlon | character varying(12) | | extended | |
the_geom | geometry | | main | |
Indexes:
"bg_pkey" PRIMARY KEY, btree (bg_id)
"idx_bg_geom" gist (the_geom) CLUSTER
Check constraints:
"enforce_dims_geom" CHECK (st_ndims(the_geom) = 2)
"enforce_geotype_geom" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
"enforce_srid_geom" CHECK (st_srid(the_geom) = 4269)
Referenced by:
TABLE "csapp_center_bgs" CONSTRAINT "csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id" FOREIGN KEY (bg_id) REFERENCES bg(bg_id) DEFERRABLE INITIALLY DEFERRED
Child tables: tiger_data.tn_bg
Has OIDs: no
这是csapp_上的\d + ...
Column | Type | Modifiers | Storage | Stats target | Description
-----------+-----------------------+---------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('csapp_center_bgs_id_seq'::regclass) | plain | |
bg_id | character varying(12) | not null | extended | |
center_id | integer | not null | plain | |
Indexes:
"csapp_center_bgs_pkey" PRIMARY KEY, btree (id)
"csapp_center_bgs_5e94e25f" btree (bg_id)
"csapp_center_bgs_c63f1184" btree (center_id)
Foreign-key constraints:
"csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id" FOREIGN KEY (bg_id) REFERENCES bg(bg_id) DEFERRABLE INITIALLY DEFERRED
"csapp_center_bgs_center_id_360e6806f7d3fee_fk_csapp_centers_id" FOREIGN KEY (center_id) REFERENCES csapp_centers(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no
这是版本:
version
------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
这是我的搜索路径。
search_path
---------------
public, tiger
(1 row)
bg在模式Tiger中，而csapp_center_bgs在模式public中...
最佳答案
我的第一个猜测是，您正在处理两个名为bg的不同表。模式tiger中的一个，而未公开模式中的另一个，则在 tiger 中的search_path之前-否则，tiger根本不在search_path中。
在当前数据库的所有模式中查找所有名为bg(区分大小写)的表:
SELECT * FROM pg_tables WHERE tablename = 'bg';
要了解
search_path设置:要了解Postgres数据库集群的结构:
如果不是那样，则您的索引可能已损坏。我首先尝试
REINDEX :REINDEX bg_pkey;
遗产!
我在添加的表定义中看到:
怀疑带有
bg_id ='470370111002'的行实际上位于子表 tiger_data.tn_bg中。但是您的FK约束引用了父表。 FK约束不被继承。查询会得到什么？
SELECT * FROM ONLY bg WHERE bg_id ='470370111002'如果我的假设成立，那么您将一无所获。阅读chapter Caveats on the Inheritance page of the manual。
有关的: