Re: [GENERAL] Syntax error needs fresh eyeballs

[Rich Shepard]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Syntax error needs fresh eyeballs

[Mike Sofen]

From: Rich Shepard
   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".

---

Just a guess...You've camel-cased the table names but aren't using double
quotes.  Try either lower-casing all table names and references to them, or
double-quoting all identifiers.  

 

Mike

 

Re: [GENERAL] Syntax error needs fresh eyeballs

[Tom Lane]

Rich Shepard  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Syntax error needs fresh eyeballs

[Rich Shepard]

  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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general