Error text: "Key ... is not present in table" even though the key is there? Using Postgresql 9.5 on Ubuntu 16.04.  Importing large .csv files into a new database using COPY FROM.  The database is created by the same script that attempts to import the .csv files, so I know the tables are initially empty.  The script successfully imports around 45,000 rows into the first table.

The problem:
Partway through importing the 2nd table's data, I get errors like this one:

ERROR:  insert or update on table "inactive" violates foreign key constraint "inactive_drug_code_fkey"
DETAIL:  Key (drug_code)=(47543) is not present in table "qrym_drug_product".


But when I check the 1st table, I see that that key exists:

select * from dpd.qrym_drug_product where drug_code=47543;
  id   | drug_code | product_categorization | class | ...
-------+-----------+------------------------+-------+----
 17970 |     47543 |                        | Human | ...
(1 row)


Why would PostgreSQL complain about that key not being present in the table, when I can see it does exist?

I'm not using table inheritance (I saw the caveat on [1]).

Here is the first table:

\d dpd.qrym_drug_product;
                                       Table "dpd.qrym_drug_product"
           Column           |  Type   |                             Modifiers                              
----------------------------+---------+--------------------------------------------------------------------
 id                         | bigint  | not null default nextval('dpd.qrym_drug_product_id_seq'::regclass)
 drug_code                  | integer | not null
 product_categorization     | text    | 
 class                      | text    | 
 drug_identification_number | text    | 
 ai_group_no                | text    | 
Indexes:
    "qrym_drug_product_pkey" PRIMARY KEY, btree (id)
    "qrym_drug_product_drug_code_key" UNIQUE CONSTRAINT, btree (drug_code)
Referenced by:
    TABLE "dpd.inactive" CONSTRAINT "inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT


Here is the definition for the 2nd table that bombs partway through the COPY FROM:

\d dpd.inactive;
                                       Table "dpd.inactive"
           Column           |  Type   |                         Modifiers                         
----------------------------+---------+-----------------------------------------------------------
 id                         | bigint  | not null default nextval('dpd.inactive_id_seq'::regclass)
 drug_code                  | integer | not null
 drug_identification_number | text    | 
 brand_name                 | text    | 
 history_date               | date    | 
Indexes:
    "inactive_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "inactive_drug_code_fkey" FOREIGN KEY (drug_code) REFERENCES dpd.qrym_drug_product(id) ON DELETE RESTRICT
Someone named "RhodiumToad" in the #postgresql channel on IRC pointed out the problem.  Because I didn't explicitly state the column name, the 2nd table is referencing the primary key column "id" from the first table.  Instead, I wanted it to reference the drug_code column.

So where I had previously defined the 2nd table as:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product,
    ...


I had to fix it up to be this:

CREATE TABLE dpd.inactive
(
    id          BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    drug_code   INTEGER NOT NULL REFERENCES dpd.qrym_drug_product (drug_code),
    ...