Column _id referenced in foreign key not found

Question

I'm going through 7 Databases in 7 Weeks.

In PostgreSQL, I created a venues table that has a SERIAL venue_id column.

output of \d venues

                                        Table "public.venues"
     Column     |          Type          |                         Modifiers
----------------+------------------------+-----------------------------------------------------------
 venue_id       | integer                | not null default nextval('venues_venue_id_seq'::regclass)
 name           | character varying(255) |
 street_address | text                   |
 type           | character(7)           | default 'public'::bpchar
 postal_code    | character varying(9)   |
 country_code   | character(2)           |
Indexes:
    "venues_pkey" PRIMARY KEY, btree (venue_id)
Check constraints:
    "venues_type_check" CHECK (type = ANY (ARRAY['public'::bpchar, 'private'::bpchar]))
Foreign-key constraints:
    "venues_country_code_fkey" FOREIGN KEY (country_code, postal_code) REFERENCES cities(country_code, postal_code) MATCH FULL

The next step is to create an event table that references venue_id with a foreign key.

I'm trying this:

CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
title text,
starts timestamp,
ends timestamp,
FOREIGN KEY (venue_id) REFERENCES venues (venue_id));

And I get this error:

ERROR: column "venue_id" referenced in forgein key not found

What's wrong?

Show output of `\d venues`. Does the table exist? Does it have a column named `venue_id`? — Craig Ringer, Jan 17 '14 at 02:42
It exists, it has a venue_id column as the primary key. I'm not on that machine right now so I'll follow up w/ that tomorrow. — quantumpotato, Jan 17 '14 at 03:05
I was able to create it with venue_id integer REFERENCES venues. Testing.. — quantumpotato, Jan 17 '14 at 18:49
Maybe this comment comes too late, but I found there's a good answer for serial type foreign key creation, https://stackoverflow.com/a/28560619/2191173 — Old Panda, Mar 27 '18 at 06:19

Meow · Answer 1 · 2014-01-17T03:42:44.517

1

you need to initialize the foreign key column too. see here http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK source & credit from @mu is too short

edited Jan 17 '14 at 03:42

answered Jan 17 '14 at 02:31

Meow

138
1
7

I don't undesrtand. Is it that I'm missing the type (int)? \n like in their example: P_Id int FOREIGN KEY REFERENCES Persons(P_Id) – quantumpotato Jan 17 '14 at 02:35

AllanLRH · Answer 2 · 2020-06-08T21:35:24.017

I'm going through the second edition of this book, so things might have changed slightly.

To create the table, you explicitly have to declare the venues_id as a column in your table, just like the rest of your columns:

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    title text,
    starts timestamp,
    ends timestamp,
    venue_id integer,  -- this is the line you're missing!
    FOREIGN KEY (venue_id)
        REFERENCES venues (venue_id) MATCH FULL
    );

Once you have executed that, the table is created:

7dbs=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | cities    | table | postgres
 public | countries | table | postgres
 public | events    | table | postgres
 public | venues    | table | postgres

Column _id referenced in foreign key not found

2 Answers2