错误：“密钥...不存在于表” [英] Error:"Key ... is not present in table"
问题描述
我有一个表中有一个字符变化（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）
参考文献tiger.bg（bg_id）MATCH SIMPLE
ON UPDATE NO ACTION删除不可撤销的行为;
这是确切的错误...
错误：表csapp_center_bgs上的插入或更新违反外键约束csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id
详细信息：键（bg_id）=（470370111002） 。
**********错误**********
错误：在表csapp_center_bgs上插入或更新违反外键约束csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id
SQL状态：23503
详细信息：Key（bg_id）=（470370111002）不存在于表bg中。
为什么这不工作？有任何想法吗？这是\d + bg ...
类型|修饰符|存储|统计目标|内容简介
---------- + ----------------------- + ---------- ---------------------------------------- + --------- - + -------------- + -------------
gid |整数| not null default nextval（'bg_gid_seq':: regclass）|平原| |
statefp |字符变化（2）| |扩展| |
countyfp |字符变化（3）| |扩展| |
tractce |字符变化（6）| |扩展| |
blkgrpce |字符变化（1）| |扩展| |
bg_id |字符变化（12）|不是null |扩展| |
namelsad |字符变化（13）| |扩展| |
mtfcc |字符变化（5）| |扩展| |
funcstat |字符变化（1）| |扩展| |
aland |双精度| |平原| |
awater |双精度| |平原| |
intptlat |字符变化（11）| |扩展| |
intptlon |字符变化（12）| |扩展| |
the_geom |几何| |主| |
索引：
bg_pkeyPRIMARY KEY，btree（bg_id）
idx_bg_geomgist（the_geom）CLUSTER
检查约束：
enforce_dims_geomCHECK（st_ndims the_geom）= 2）
enforce_geotype_geomCHECK（geometrytype（the_geom）='MULTIPOLYGON':: text OR the_geom IS NULL）
enforce_srid_geomCHECK（st_srid（the_geom）= 4269）
引用：
TABLEcsapp_center_bgsCONSTRAINTcsapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_idFOREIGN KEY（bg_id）REFERENCES bg（bg_id）DEFERRABLE INITIALLY DEFERRED
子表：tiger_data.tn_bg
有OID：no
这里是\d + on csapp _...
Column |类型|修饰符|存储|统计目标|内容提要
----------- + ----------------------- + --------- -------------------------------------------------- ---- + ---------- + -------------- + -------------
id |整数| not null default nextval（'csapp_center_bgs_id_seq':: regclass）|平原| |
bg_id |字符变化（12）|不是null |扩展| |
center_id |整数|不是null |平原| |
索引：
csapp_center_bgs_pkeyPRMARY KEY，btree（id）
csapp_center_bgs_5e94e25fbtree（bg_id）
csapp_center_bgs_c63f1184btree（center_id）
外键约束：
csapp_center_bgs_bg_id_65c818f360c84dc5_fk_bg_bg_id外键（bg_id）参考文献BG（bg_id）DEFERRABLE INITIALLY DEFERRED
csapp_center_bgs_center_id_360e6806f7d3fee_fk_csapp_centers_id外键（center_id）参考csapp_centers（ID）DEFERRABLE INITIALLY DEFERRED
具有的OID：没有
以下是版本：
version
-------------------------------------- -------------------------------------------------- --------------
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu，由gcc编译（Ubuntu 4.8.2-19ubuntu1）4.8.2，64位
这里是我的搜索路径....
search_path
---------------
public，tiger
（1 row）
bg是在模式tiger和csapp_center_bgs是在模式public ...
我的第一个猜测是你正在处理两个不同的表，名为 bg 。一个在 tiger 的模式中，另一个在未公开的模式中， tiger 在 search_path - 或tiger不在 search_path 中。
在当前db中的所有模式中查找名为 bg （区分大小写）的所有表：
SELECT * FROM pg_tables WHERE tablename ='bg';
要了解 search_path 设置： p>
要了解Postgres DB群集的结构：
如果不是这样，您的索引可能已损坏。我会先尝试 REINDEX ：
REINDEX bg_pkey;
继承！
添加表格定义：
子表：tiger_data.tn_bg
怀疑 bg_id ='470370111002'的行实际存在于子表 tiger_data.tn_bg 中。但是您的FK约束引用了父表。 FK约束不会继承。
如果查询：
SELECT * FROM ONLY bg WHERE bg_id ='470370111002' 如果我的假设成立， 。阅读章节注意事项
相关：
I have a table with a character varying(12) field in it which is its PRIMARY KEY. I ran this query
SELECT * FROM bg WHERE bg_id ='470370111002'
It selects a row from the table. All looks good. Then I try.
INSERT INTO csapp_center_bgs(bg_id,center_id) VALUES('470370111002',2)
There is a foreign key on bg_id that looks like...
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;
Here is the precise error...
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".
Why did this not work?! Any ideas? Here is \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
And here is \d+ on csapp_...
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
Here is the version:
version
------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
And here is my search path....
search_path
---------------
public, tiger
(1 row)
bg is in schema tiger and csapp_center_bgs is in schema public...
My first guess would be that you are dealing with two different tables named bg. One in the schema tiger, and another one in an undisclosed schema that comes before tiger in your search_path - or tiger is not in the search_path at all.
Find all tables named bg (case sensitive) in all schemas in the current db:
SELECT * FROM pg_tables WHERE tablename = 'bg';
To understand the search_path setting:
To understand the structure of a Postgres DB cluster:
If that's not it, your index may be corrupted. I would first try a REINDEX:
REINDEX bg_pkey;
Inheritance!
I see in your added table definition:
Child tables: tiger_data.tn_bg
Suspecting that the row with bg_id ='470370111002' actually lives in the child table tiger_data.tn_bg. But your FK constraint references the parent table. FK constraints are not inherited.
What do you get if you query:
SELECT * FROM ONLY bg WHERE bg_id ='470370111002'If my hypothesis holds, you get no row. Read the chapter Caveats on the Inheritance page of the manual.
Related:
这篇关于错误：“密钥...不存在于表”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
