From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Syntax error needs fresh eyeballs |
Date: | 2016-12-26 23:37:10 |
Message-ID: | 18921.1482795430@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Rich Shepard <rshepard(at)appl-ecosys(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Sofen | 2016-12-26 23:40:30 | Re: Syntax error needs fresh eyeballs |
Previous Message | Rich Shepard | 2016-12-26 23:23:29 | Syntax error needs fresh eyeballs |