Error:"Key ... is not present in table"

Question

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...

Answer 1

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:

• How does the search_path influence identifier resolution and the "current schema"

To understand the structure of a Postgres DB cluster:

• What's the difference between a catalog and a schema in a relational database?

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:

• Use triggers on inherited tables to replace foreign keys