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