列_id未找到外键引用

Question

我将在7周内浏览7个数据库。

在 PostgreSQL 中，我创建了一个具有串行venue_id列的地点表。

< code>\d场馆的输出

                                        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

下一步是创建一个用外键引用venue_id的事件表。

我在尝试这个:

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

我得到这个错误：

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

怎么了？

Answer 1

我正在阅读本书的第二版，所以事情可能会略有变化。

要创建表，您必须明确地将venues_id声明为表中的列，就像其余列一样：

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
    );

执行该操作后，将创建该表：

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

Answer 2

您还需要初始化外键列。看这里http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK来源