All geek questions in one place

Error: "Key ... not in the table"

I have a table with a variable (12) in it with a character, which is its MAIN KEY. I fulfilled this request

SELECT * FROM bg WHERE bg_id ='470370111002'

He selects a row from the table. Everything 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 exact 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 is this not working ?! Any ideas? Here \ 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 \ 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 the tiger schema, and csapp_center_bgs is in the public schema ...

+5
source share
1 answer

My first assumption was that you are dealing with two different tables named bg . One in the tiger schema, and the other in the undisclosed schema that precedes the tiger in your search_path , or the tiger is not in the search_path at search_path .

Find all tables named bg (case sensitive) in all schemes of the current db:

 SELECT * FROM pg_tables WHERE tablename = 'bg';

To understand the search_path setting:

To understand the Postgres DB cluster structure:

If this is not the case, your index may be corrupted. First I'll try REINDEX :

 REINDEX bg_pkey;

Inheritance!

I see in your added table definition:

 Child tables: tiger_data.tn_bg

Suspecting that a row with bg_id ='470370111002' really lives in a child table tiger_data.tn_bg . But your FK constraint refers to the parent table . FK restrictions are not inherited. What will you get if you request:

 SELECT * FROM ONLY bg WHERE bg_id ='470370111002'

If my hypothesis is correct, you will not get a row. Read the Cautions chapter on the Inheritance Guide page .

on this topic:

+4
source

Source: https://habr.com/ru/post/1210816/

More articles:


All Articles