Re: ERROR: UNIQUE constraint matching given keys for

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.")