Обсуждение: [GENERAL] Syntax error needs fresh eyeballs

   My schema includes three tables and psql throws an error I'm not seeing
when I try to read the schema into the database. I'd appreciate fresh eyes
looking at the table and learning what error I'm not seeing.

   The tables:

CREATE TABLE Weather (
   site_id INTEGER PRIMARY KEY,
   site_name TEXT,
   site_location TEXT
);


CREATE TABLE Weather_Params (
   site_id INTEGER
     REFERENCES Weather(site_id),
   param TEXT,
   param_unit TEXT,
   freq INTEGER,
   freq_unit TEXT,
   equip TEXT,
   PRIMARY KEY (site_id, param)
);


CREATE TABLE Weather_Data (
   site_id INTEGER
     REFERENCES Weather(site_id),
   monit_date DATE,
   monit_time TIME,
   read_by TEXT,  -- name of employee collecting data
   param TEXT
     REFERENCES Weather_Params(param),
   param_value REAL,
   PRIMARY KEY (site_id, monit_date, monit_time, param)
);

   The error:

ERROR:  there is no unique constraint matching given keys for referenced
table "weather_params".

TIA,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:
>    My schema includes three tables and psql throws an error I'm not seeing
> when I try to read the schema into the database. I'd appreciate fresh eyes
> looking at the table and learning what error I'm not seeing.

Hm, seems straightforward enough to me: the only uniqueness constraint
you've got in Weather_Params is

>    PRIMARY KEY (site_id, param)

but in Weather_Data you write

>    param TEXT
>      REFERENCES Weather_Params(param),

so you get this

> ERROR:  there is no unique constraint matching given keys for referenced
> table "weather_params".

because Weather_Params.param isn't constrained to be unique.
Weather_Params' pkey constrains the combination of site_id and param to be
unique, but that doesn't make param alone unique, so param in Weather_Data
isn't enough to reference a well-defined row of Weather_Params.

Seeing that Weather_Data also has a site_id column, I'm going to guess
that what you wanted to put in Weather_Data is a two-column FK:

     FOREIGN KEY (site_id, param) REFERENCES Weather_Params (site_id, param)

That would match Weather_Params' pkey, so it's enough to identify a
unique row of Weather_Params.

            regards, tom lane

On Mon, 26 Dec 2016, Tom Lane wrote:

>> ERROR:  there is no unique constraint matching given keys for referenced
>> table "weather_params".
>
> because Weather_Params.param isn't constrained to be unique.
  ...
> Seeing that Weather_Data also has a site_id column, I'm going to guess
> that what you wanted to put in Weather_Data is a two-column FK:
>
>     FOREIGN KEY (site_id, param) REFERENCES Weather_Params (site_id, param)
>
> That would match Weather_Params' pkey, so it's enough to identify a
> unique row of Weather_Params.

   Thanks, Tom. Yes, I do want a 2-column FK.

Much appreciated,

Rich