Thread: ERROR: UNIQUE constraint matching given keys for referenced table "rl_voltas" not found

Hi! I'm a beginer in PosrgreSQL, and I have a problem I can't resolve.
 
The script is the following :
 
CREATE TABLE RL_RefVoltas(
 NVolta SERIAL NOT NULL PRIMARY KEY, 
 Designacao VARCHAR(15) NOT NULL, 
 Tipo INT NOT NULL  
);
 
CREATE TABLE RL_Voltas(
 NVolta INT NOT NULL, 
 Data DATE NOT NULL, 
 Hora TIME NOT NULL,
 Matricula VARCHAR(9),
 NLitros INT,
 PRIMARY KEY (Data,Hora),
 foreign key (NVolta)references RL_RefVoltas(NVolta)
);
 

CREATE TABLE RL_Cisterna(
 NVolta INT NOT NULL,
 Data DATE NOT NULL, 
 Hora TIME NOT NULL, 
 NCist INT NOT NULL PRIMARY KEY, /*1 a 4*/
 Acidez INT, 
 ProvaAlcool BOOL,
 TFermentacao TIME,
 Fermentacao FLOAT,
 foreign key (NVolta, Data, Hora) references RL_Voltas(NVolta, Data, Hora)
);
 
The result of executing this script is :
 
ERROR:  UNIQUE constraint matching given keys for referenced table "rl_voltas" not found
 
Anybody can tell me why?
 
Thanks, and good work.

Re: ERROR: UNIQUE constraint matching given keys for

From
Stephan Szabo
Date:
On Wed, 16 Jul 2003, Rui Pires wrote:

> Hi! I'm a beginer in PosrgreSQL, and I have a problem I can't resolve.
>
> The script is the following :
>
> CREATE TABLE RL_RefVoltas(
>  NVolta SERIAL NOT NULL PRIMARY KEY,
>  Designacao VARCHAR(15) NOT NULL,
>  Tipo INT NOT NULL
> );
>
> CREATE TABLE RL_Voltas(
>  NVolta INT NOT NULL,
>  Data DATE NOT NULL,
>  Hora TIME NOT NULL,
>  Matricula VARCHAR(9),
>  NLitros INT,
>  PRIMARY KEY (Data,Hora),
>  foreign key (NVolta)references RL_RefVoltas(NVolta)
> );
>
>
> CREATE TABLE RL_Cisterna(
>  NVolta INT NOT NULL,
>  Data DATE NOT NULL,
>  Hora TIME NOT NULL,
>  NCist INT NOT NULL PRIMARY KEY, /*1 a 4*/
>  Acidez INT,
>  ProvaAlcool BOOL,
>  TFermentacao TIME,
>  Fermentacao FLOAT,
>  foreign key (NVolta, Data, Hora) references RL_Voltas(NVolta, Data, Hora)

There is no unique constraint on RL_Voltas defined that mentions all three
of the columns mentioned here. That is a requirement for being able to
make a foreign key to that set of columns (I think SQL92 11.8 SR2a covers
it with " If the <referenced table and columns> specifies a <reference
column list>, then the set of column names of that <reference column list>
shall be equal to the set of column names in the unique columns of a
unique constraint of the referenced table.")